SUMIF Multiple Criteria Google Sheets (Easiest Way in 2023)
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:
SUMIF Multiple Criteria Google Sheets
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
1. Select an Empty Cell
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.
2. Use the SUMIFS Function in the cell
The basic syntax of the "SUMIFS" function is as follows:
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
3. Press Enter
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.
Dynamic SUMIFS with Multiple Criteria in Google Sheets
1. Determine your Static Values
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
2. Determine your Dynamic Values
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 2: G$1 (Changing Column, Static Row) (Profit or Expense)
3. Adjust your SUMIFS Formula for your Dynamic and Static Cells
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.
4. Drag your Formula to the Adjacent Cells
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.