In this article we will show how to use SUMIF with multiple criteria in Google Sheets using the SUMIFS function. Simply follow the steps below:
Once you learn how to use the SUMIF function, it might be tempting to use it for multiple criteria. However, one limitation of the SUMIF function is that it will only work for one criterion for each use.
If you want to be able to add values based on multiple criteria and have them dependent on other cells, the SUMIFS function will help make your formula shorter and easier to use.
The example worksheet in this article can be accessed here: SUMIFS Sample
For our example, we have several teams with two types of cash flows: profits and expenses. We want to be able to add total profits for each team and subtract the sum of all expenses.
We’ll select Cell G2 for our formula.
The basic syntax of the "SUMIFS" function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Sum_range: The range of cells you want to add up. Here we want to add up the values in Column A, A:A.
Criteria_range: This is the range of cells where your first criteria will be applied. We want to apply our first criteria to Column B, B:B.
Criteria1: You first criterion to check if the cells in the criteria_range are a fit. If we want to sum up the profit for team A, our first criteria will be to check if the cells in Column B are from team A. We will use “A” for Criteria 1.
Criteria_range2: This is the next set of ranges that our second criteria will be applied to. Our second criteria will be applied to Column C, C:C.
Criteria2: The basis for summing values in the sum_range dependent on criteria_range2. Since we want to sum profits, our second criteria is “profit”.
Following this breakdown, we will be using the formula
=sumifs(A:A,B:B,"A",C:C,"Profit")
We have successfully summed values that meet our criteria.
You can add more criteria ranges and criteria pairs as needed by adding each after a new comma.
The SUMIFS function will only sum values that meet ALL the specified criteria in the formula.
Now let’s complete the profit vs. expense table by creating a dynamic formula we can apply to other cells by simply dragging the formula.
Static Values: Values that we don’t want to change even if we move our formula around.
We’ll use the dollar sign “$” to tell Google Sheets what we want to be static.
For our example, we want our Sum Range, Criteria Range 1 and Criteria Range 2 to be fully static as these will always apply to their specified ranges. To make them fully static, we will adjust their forms to include dollar signs “$” before both the column letter and row number.
Our new input ranges will be as follow:
Sum Range: $A:$A or $A$2:$A$18
Criteria Range 1: $B:$B or $B$2:$B$18
Criteria Range 2: $C:$C or $C$2:$C$18
Dynamic Values: Values that we want to change based on the location of the cell.
Our dynamic values will be used to check the criteria for which team letter and what type of cash flow (profit or expense) is recorded. Instead of using direct values for our criteria, we will use cell references. We can see that by using the table format, we can easily reference the team and the cash flow type.
Given our table format, we will have partially dynamic cells. Since we want our criteria 1 to always reference cells in Column F, We will make Column F static. Criteria 2 will also always depend on values in Row 1, which is why we will make Row 1 static.
We will put the dollar sign in our cell reference before the non-moving elements as follows:
Criteria 1: $F2 (Static Column, Changing Row) (Team Letter)
Criteria 2: G$1 (Changing Column, Static Row) (Profit or Expense)
Following our adjustments, the SUMIFS formula for our example should now be =SUMIFS($A:$A,$B:$B,$F2,$C:$C,G$1)
Note that G$1 references the header “profit” which then sums cells that have this corresponding profit tag in column C in the table of the left. In the expenses column the formula references H$1 and sums values with this tag in column C.
Drag the small blue box at the lower right corner of your cell down and across all other cells. You can also copy and paste your formula.
You can see that it automatically populates the cells without the need to write a specific formula for each cell.
Using SUMIFS with dynamic values for adding values accommodating multiple criteria can make your workflow in Google Sheets a lot easier. You can also adjust your formula to include different types of values and more complex criteria. This is how powerful the SUMIFS function can be.
If you enjoyed this article, you might also like our article on the SUMIF if formula in Google Sheets or our article on Google Sheets SUMIFS examples.
If you want to learn how to send mass emails from Google Sheets, we also suggest checking out our detailed guide.
