Another awesome logical sum tutorial. If you want to know how you use SUMIF when multiple criteria in the same column in Google Sheets, follow this tutorial.
There are many SUMIF formula related tutorials on Info Inspired. Hope you may find time to use the search field on the top to read those awesome tutorials. Now about our topic.
When you want to use multiple criteria in the same column in SUMIF, probably you are doing like this.
You may first use a single criterion in one SUMIF formula and then another SUMIF formula with another criterion. Finally, you can add both the SUMIF formulas.
SUMIF(range, criterion, sum_range) + SUMIF(range, criterion, sum_range)
No doubt it’s workable. But there is a 100% better solution in this case. There is an alternative way to SUMIF when multiple criteria in the same column in Google Sheets.
Here is that tip. First, see the above said normal formula (multiple Sumif formulas) below with an example. So that you can understand the benefit of using my recommended formula.
SUMIF When Multiple Criteria in One Column – Normal Way
Sample Data to learn the use of SUMIF when multiple criteria in the same column in Google Sheets.
Here I want the sum of the total of the fruits “Apple” and “Peer”. The criteria are highlighted in Yellow color in Column A and the Totals are highlighted in Cyan in Column E. I’m making the total with the help of two individual Sumif formulas as below.
=sumif(A2:A7,"Apple",E2:E7)+sumif(A2:A7,"Peer",E2:E7)
Result: 22
The first Sumif formula finds the total of the fruit “Apple” and the second one finds the total of “Peer”. Now here is my recommended SUMIF formula.
SUMIF When Multiple Criteria in the Same Column in Google Sheets – Array Based
This formula is the recommended SUMIF formula in this case.
=sum(ArrayFormula(sumif(A2:A7,{"Apple";"Peer"},E2:E7)))
Result: 22
Here, as you can see, I’ve included both the criteria in one Sumif. For that, I’ve used Curly Brackets. And additionally, I’ve used Array Formula and Sum here.
You can use this formula to Sumif when multiple criteria in the same column in Google Sheets.
Note: If you omit the function SUM in this formula, you would get the total of the fruits “Apple” and “Peer” in separate cells.
Why is this important? Just read on. The below is an additional tip and little bit off topic.
Generate Group Wise Summary Using SUMIF in Google Sheets
You can use the above array based SUMIF formula, excluding the SUM in that, to create a group-wise summary of the fruit items. See the following image.
In column C10, I’ve applied a Unique formula to extract the unique fruits’ names. It’s not revealed in the image as I’ve concentrated on the SUMIF formula. Below is that Unique formula.
=unique(A2:A7)
This Unique formula returned the fruits names in the range C10: C13 after removing duplicates.
Similar: Find and Eliminate Duplicates Using Query Formula in Google Sheets
This I want to use as multiple criteria in SUMIF. In the Cell D10, I’ve applied the below SUMIF formula that returns the total of the fruit items in D10: D13. Please again take a look at the above image.
=ArrayFormula(sumif(A2:A7,{C10:C13},E2:E7))
For Advanced Users:
You can further shorten this grouping by joining the UNIQUE and SUMIF in one formula. Here it’s.
The cell references are replaced by two Unique function based formulas which return the values in the cell C10: C13.
={unique(A2:A7),ArrayFormula(sumif(A2:A7,{unique(A2:A7)},E2:E7))}
If I go for further explanation, you may not understand anything. Better you should apply all these formulas one by one in your sheet. So that you can quickly learn the above tips. That’s all.
Hope you could learn how to Sumif when multiple Criteria in the same column in Google Sheets and SUMIF in the grouping.
