How to Use SUMIFS Function in Google Sheets (Multiple Criteria)

If you want to find the sum of values based on a condition, you most probably are aware of the SUMIF and the SUMIFS function in Google Sheets.

The problem with the SUMIF function is that it lets you specify just one condition.

However, in many cases, conditions are not so black and white. We often need to find sums based on more than one condition. In such cases, the SUMIFS function can be quite handy.

In this tutorial, I will show you how to use the SUMIFS function in Google Sheets using some practical examples.

What does the SUMIFS Function do?

Simply put, the SUMIFS function finds the sum of values that satisfy more than one condition.

The function scans through a range of cells and retrieves those that match all the conditions specified.

Once it has scanned through the range, it sums up the retrieved values and displays the result.

To give you an example, if you have a list of sales transactions and you want to know the sum of all transactions in a specific date range, you can do that with SUMIFS.

Syntax of the SUMIFS Function

The general syntax for the SUMIFS function is:

 =SUMIFS(sum_range, criteria_range1, criteria1,[ criteria_range2, criteria2, ... criteria_range_n, criteria_])

Here, 

  • sum_range is the range of cells containing the values you want to test.
  • criteria_range1 is the range to check for criteria1.
  • criteria1 is the condition that criteria_range1 needs to satisfy.
  • criteria_range2, criteria2, etc. are additional ranges and criteria for checking.

We can add as many criteria as we need to.

However, it goes without saying that there needs to be at least 1 criteria range and criteria parameter in the function.

How to Use SUMIFS Function in Google Sheets

The function syntax will become clearer once we work on a few examples.

In the table below there are 8 employees, along with their departments, location, date of joining, number of hours worked, and money raised from sales.

Dataset for SUMIFS function

Let us look at a few scenarios using this data.

Using SUMIFS with Text Conditions

Let us say you want to find the total hours worked by all employees of the Manufacturing department in New York. In this case, we have two conditions:

  • Department= “Manufacturing”
  • Location= “New York”

 So the parameters for the SUMIFS function will be as follows:

  • sum_range will include the cells in locations E2:E9 – The hours worked
  • criteria_range1 will include cell locations B2:B9 – The department
  • criteria1 will be “Manufacturing”, since we want to select cells where department= “Manufacturing
  • criteria_range2 will include cell locations C2:C9 – The location
  • criteria2 will be “New York”, since we want to select cells where location= “New York”.

Therefore, you can type the following formula in the formula bar:

=SUMIFS(E2:E9,B2:B9,"Manufacturing",C2:C9,"New York")

Here’s what you get as the result:

Total hours worked using SUMIFS formula

Explanation of the Formula

In the above case, the SUMIFS function checked each cell from B2 to B9 and C2 to C9 to find cells that satisfy both the conditions – “Manufacturing” and “New York” respectively.

For each matching row, the function selected the corresponding hours worked value from column E.

It then added up all the selected hours worked values and displayed the result in cell C13.

SUMIFS formula explained

Using SUMIFS with Date Condition

Let us add one more condition. Let’s say we also want to add the criteria  that the employee’s date of joining should be before Jan 1st 2020.

So now we have three conditions:

  • Department = “Manufacturing”
  • Location = “New York”
  • Joining date < 01/01/2020

That means we need to add two more parameters to the SUMIFS function:

  • criteria_range3 will include cell locations D2:D9 – The joining date
  • criteria3 will be “<01/01/2020”, since we want to select cells where joining date< “01/01/2020”

So, you can type the following formula in the formula bar (notice the last 2 parameters that have now been added):

=SUMIFS(E2:E9,B2:B9,"Manufacturing",C2:C9,"New York",D2:D9,"<01/01/2020")

Here’s what you get as the result:

Explanation of the Formula

In the above case, the SUMIFS function checked each cell from B2 to B9, C2 to C9, and D2 to D9 to find cells that satisfy all three conditions – “Manufacturing”, “New York” and “<01/01/2020” respectively.

For each matching row, the function selected the corresponding hours worked value from column E.

It then added up all the selected hours worked values and displayed the result in cell C14.

SUMIFS formula with three conditions explained

Using SUMIFS with Number Condition

Finally let us look at a case where we want to know the total sales of Manufacturing employees who have worked for 10 hours or more.

So, we now have two conditions:

  • Department= “Manufacturing”
  • Hours worked>= 10

The parameters for the SUMIFS function will be as follows:

  • Since we now want the total sales , the sum_range will include the cells in locations F2:F9
  • criteria_range1 will include cell locations B2:B9 – The department
  • criteria1 will be “Manufacturing”, since we want to select cells where department= “Manufacturing”
  • criteria_range2 will include cell locations E2:E9 – The hours worked
  • criteria2 will be “>=10”, since we want to select cells where hours worked>=10

To get the correct result, you can type the following formula in the formula bar:

=SUMIFS(F2:F9,B2:B9,"Manufacturing",E2:E9,">=10")

Here’s what you get as the result:

SUMIFS formula to calculate total hours worked

Explanation of the Formula

In the above case, the SUMIFS function checked each cell from B2 to B9 and E2 to E9 to find cells that satisfy both conditions – “Manufacturing” and “>=10” respectively.

For each matching row, the function selected the corresponding sales value from column F.

It then added up all the selected sales values and displayed the result in cell C13.

SUMIFS formula explained for total hours worked

Points to Remember

In using the SUMIFS function, there are a few important points that you should keep in mind:

  • The SUMIFS function is not case-sensitive.
  • If the criteria contains a text value, a date or wildcards, then it must be enclosed in double quotes.
  • Cell references in the criteria are not enclosed in quotes.
  • You need to have at least 1 pair of criteria_range and criteria parameters in your SUMIFS function

The SUMIFS function is so versatile and customizable that you can include any number of conditions you want.

I encourage you to incorporate the SUMIFS function into your spreadsheets whenever you need to find the sum based on more than one condition.

Hope this was helpful.

Other Google Sheets tutorials you may find useful:

Leave a Comment