Easy SUMIFS Google Sheets Guide (Multiple Criteria)

Last updated April 22, 2022

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

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 Google Sheets 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 – How to Use SUMIFS in Google Sheets

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.

SUMIFS Formula Google Sheets Rules

Here are a few things to keep in mind when formulating a SUMIFS calculation:

  • SUMIFS is for multiple conditions. Use SUMIF for single condition calculations
  • You can use text, dates, and numbers as the criteria
  • You can use wildcards (?,*) with SUMIFS calculations
  • You can use operators with SUMIFS Functions
  • Additional ranges must have matching rows and columns as the sum_range
  • You cannot use an ARRAY with SUMIFS functions
  • Text strings should be in quotes, cell references shouldn’t be

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.

SUMIFS Google Sheets Example 1 – Using 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 SUMIFS and Logic in This 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

Multiple Conditions in One Column (SUMIFS OR Google Sheets Logic)

Unfortunately, there isn’t a way for Google Sheets to SUMIF multiple criteria into one range. Let’s pretend you had a payroll error and you need to check which employees were paid incorrectly. SO you made a spreadsheet like the one below.

An example spreadsheet to check employee payroll issues with sumifs

You may want to include “Yes” and “Unknown” as criteria for your SUMIFS calculation. But, this would return an error.

An example of a faulty SUMIFS formula
An error box for a wrong SUMIFS formula

The simplest way to use OR logic in SUMIFS is to add two calculations together. In this example, you could just copy the same calculation twice but with one calculation using “Yes” as criteria and the other using “No” like so:

=SUMIFS(C2:C7,B2:B7,"Yes",D2:D7,"Yes") + SUMIFS(C2:C7,B2:B7,"Unknown",D2:D7,"Yes")

Google Sheets SUMIFS multiple criteria into a final correct answer with this method.

A screenshot showing the final result for addisng two SUMIFS functions together

Google Sheets SUMIFS FAQ

Can You Add Two SUMIFSTogether? / Can You Use or With SUMIFS?

Instead of using a direct “or logic” input into the SUMIFS you have to add them together. So, the answer to both these questions is yes! Here is an example of how you would do it:

 =SUMIFS(C2:C7,B2:B7,"Yes",D2:D7,"Yes") + SUMIFS(C2:C7,B2:B7,"Unknown",D2:D7,"Yes") 

How Do You Do Sumifs With Multiple Criteria in Google Sheets?

If it’s multiple criteria in one column, you have to add two SUMIFS calculations together like in the example above. For “and logic” (multiple columns) add each new criteria and range to one SUMIFS calculation like so:

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

Where “Manufacturing” and “New York” are the criteria and the ranges to their left are the cells you want to the SUMIFS formula to pull from.

SUMIFS Sheets 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 Google Sheets 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:

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

Leave a Comment