How Sumif Function Differed in Excel and Google Sheets
Here is yet another comparison of Excel and Google Sheets functions. This time the function in question is Sumif, the popular logical sum function. Let me shed some light on some of theSumif function differences in Excel and Google Sheets.
If you know how to use Sumif in Google Sheets or Sumif in Excel, then you can go ahead using them as it is in both of the applications. An approx. 95% of the time (or more?), you will find the formulas working without any flaws.
You might have read/heard that the usage of Sumif in Google Sheets and Sumif in Excel is the same. I also thought there is no any scope of a Sumif Excel vs. Sumif Google Sheets comparison. But in advanced use, the Sumif is slightly different in Google Sheets and Excel.
Before going to address the differences in the use of Sumif in Google Sheets and Excel, I am just sharing you how to use Sumif in Excel/Google Sheets. Its only the basic use. For advanced use please refer to my Sumif in Google Sheets tutorial which is applicable to Excel also.
Must Check: Google Sheets Functions Guide.
Sumif Basic Use in Excel and Google Sheets
Here in this section, I am talking about the similarities of the function Sumif in Excel and Sumif in Google Sheets.
As on today, there are 20+ Google Sheets tutorials related to the use of Sumif in Google Sheets on this blog. You can find that here. No doubt many of them will equally work in Excel.
Since all of my previous Sumif tutorials are tested in Google Sheets, this time I am testing a few of them in Excel. So, of course, the screenshots presented will be from Excel but will work on both the applications.
Syntax:
SUMIF(range, criterion, [sum_range])
Understand what is therange, criterion, and sum_range in Sumif with the help of the following examples.
Text Criterion in Sumif in Excel/Google Sheets
In the below example, let me show you the basic use of Sumif in Excel/Google Sheets with text value as thecriterion.
How to sum the values in column F only for the item Orange? I dont want to sum all the values in the range F3: F8. What I want to sum is the values in the cells F4 and F8.
So the condition or criterion here is Orange. I have marked the range and sum_range in the below screenshot.
The equivalent formula when the criterion used within the formula.
=SUMIF(B3:B8,"Orange",F3:F8)
Thats how the text criterion is used in Sumif in both Excel and Google Docs Sheets.
Date Criterion in Sumif in Excel/Google Sheets
Sum the quantity in column E if purchase date in column C is 17/10/18.
range: B3: B8
criterion: D12
sum_range: E3: E8
If you want the criterion within the formula, it should be used as below.
=SUMIF(C3:C8,DATE(2018,10,17),E3:E8)
Please take a note on how the Date function used to enter date criteria in Sumif. Here again, one cant point out or find anySumif function differences in Excel and Google Sheets
Number Criterion in Sumif in Excel/Google Sheets
Note: If you check the Sumif syntax, both in Excel and Google Sheets, you can see that the sum_range is optional. If range and sum_range are the same, then you can skip the sum_range in Sumif.
This above Sumif formula sums the column E if the Qty. in column E is 100.
The following Sumif formula would sum the numbers in column Fif the Qty. in column E is 100. The criterion is in cell H2.
=SUMIF(E3:E8,H2,F3:F8)
The Number as criterion within Sumif formula.
=SUMIF(E3:E8,100,F3:F8)
Comparison Operators in Sumif in Excel/Google Sheets
The use of comparison operators in Sumif is also similar in Excel and Google Sheets. Here are the 6 comparison operators and their use in Sumif.
I am going minimal here. I mean here the range and sum_range are the same.
The above Sumif examples show the use of comparison operators in it. But in that, the criteria are used in the formula as cell references. When you include the comparison operators in Sumif in Excel or Google Sheets, it should be placed within double quotes as below.
Sumif with Greater Than Operator
I am just posting the formulas. Please check the criterion in it.
=SUMIF($B$2:$B$7,">"&250)
or
=SUMIF($B$2:$B$7,">250")
Both the above formulas would return the same result. So you can use the convenient one. I like the second formula and following that in the below examples.
Sumif with Less Than Operator
=SUMIF($B$2:$B$7,"<500")
Sumif with Equal to Operator
=SUMIF($B$2:$B$7,250)
Sumif with Greater Than or Equal to Operator
=SUMIF($B$2:$B$7,">=250")
Sumif with Less Than or Equal to Operator
=SUMIF($B$2:$B$7,"<=250")
Sumif with Not Equal to Operator
=SUMIF($B$2:$B$7,"<>500")
Wildcards in Sumif in Excel/Google Sheets
Wildcard Characters like *, ?,~ (asterisk, question mark, tilde) equally work well in Sumif in Excel and Sumif in Google Sheets. That again gives no room for me to go for Sumif Excel/Sheets comparison.
I am not detailing the usage here. Instead, check my following tutorial.How to Use Wildcard Characters in Google Sheets Functions.I have detailed there how to use wildcards in Sumif which is applicable in Excel also.
Sumif Function Differences in Excel and Google Sheets
Finally, we are here!
The differences that I am going to point out persists at the time of writing this post.
The Difference in The Use of Range in Sumif in Google Sheets and Excel
The range argument in Excel Sumif must be a physical range, not a virtual range created using any other formulas.
In Google Sheets, on the contrary, you can use other formula outputs as the range in Sumif. This makes Google Sheets Sumif more powerful compared to the Excel Sumif.
Example 1: Sum by Month/Year in Excel and Google Sheets
To Sum by Month in Google Sheets you can use the Sumif function. In Excel, as far as I know, the Sumif may not work. There instead you can use the Sum and If combination formula.
Google Sheets Sumif with Date Range:
The above Sumif array formula sums the values in column Fwherever the date of join (column E) months are 5.
I have used the Month function in Sumif range to return the month numbers. It must be entered as anArray Formula as the function Month cant return an array result by itself.
Must Check: Google Sheets Date Functions (The Complete Guide).
It wont work in Excel. Here is the Excel Sumif alternative using Sum and IF.
=SUM(IF(MONTH($E$3:$E$7)=5,$F$3:$F$7,0))
Enter this formula also as an array formula. So enter it with the Ctrl+Shift+Enter key combination.
You May Like:Array Formula: How It Differs in Google Sheets and Excel.
That means you can use the other date functions in Sumif range in Google Sheets, but not in Excel.
This is one of the major Sumif function differences in Excel and Google Sheets. You can use date functions like MONTH, WEEKNUM, YEAR etc. as the range in SUMIF in Google Sheets.
Example 2: Virtual Range Using Row Function in Sumif
As I have explained no virtual range is entertained in Sumif in Excel. Dont think that its limited to the date functions as above.
Here is one more example that clearly depicts the Sumif function differences in Excel and Google Sheets.
You can do cumulative sum calculation using Sumif in Google Sheets, not in Excel. This is due to the above same range usage restriction in Excel Sumif.
I have demonstrated that in an individual tutorial here Normal and Array-Based Running Total Formula in Google Sheets(in this I have used a Sumif formula to calculate the cumulative sum)
If you check that tutorial you can see that I have used a Row formula range as the Sumif range.
In Excel, I have forced to use MMULT since the Sumif doesnt support Running Total Array Formula in Excel.
Example 3: The Use of Ampersand Sign in SUMIF
In Sumif function in Google Sheets, you can use the ampersand to combine columns and also criteria.
I think Excel must incorporate this feature as it can even replace SUMIFS in many cases! But I am unsure about the latest version of Excel since I dont have that version to test.
What is that Ampersand use in Sumif in Google Sheets?
I have detailed that in this Sumifs (yes, Sumifs) tutorial Google Sheets: Sumifs Array Formula Expanding Issue and Alternative Formulas.
Conclusion:
Thats all. I hope I could successfully bring into light theSumif function differences in Excel and Google Sheets which many thinks doesnt exist.
Thanks for the stay, and Enjoy!
- TAGS
- Excel
- Excel Vs Sheets
- Google Doc
- Sumif Google Sheets