Simple SUMIFS Google Sheets Guide (Multiple Criteria)

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

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.

SUMIF formula with three conditions

What Does the SUMIFS Google Sheets Function Do?

Simply put, the SUMIFS function finds the sum of values that satisfy more than one condition. Unlike the SUMIF function which only does one at a time.

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 the SUMIF formula 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 – How to Sum With Multiple Criteria Using AND Logic

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 Google Spreadsheet 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:

SUMIFS formula with three conditions explained

Explanation of the Formula

In the above Google Sheets SUMIFS multiple criteria example, the 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.

Related Reading: How to SUM a Column in Google Sheets

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")

The Google SUMIFS function handles multiple criteria into a final correct answer with this method.

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

Using the SUMIFS Function With Blank and Non Blank Cells

You can use the SUMIFS function with operators to sum cells if they contain no data or any data. Here’s an example formula for searching blank cells by using a space between quotes:

=SUMIFS(A3:A33,B3:B33,"Paid",C3:C33," ")

This formula will only sum if there is an empty cell in column c for the corresponding row and the customer is marked as Paid in the other column. You can apply the same logic and the not blank operator (<>) to sum cells too, like so:

=SUMIFS(A3:A33,B3:B33,"Paid",C3:C33,"<>")

Related Reading: Google Sheets DSUM Function

Using Wildcards with SUMIFS

As we touched on earlier, you can use wildcards in SUMIFS calculations. Wildcards are operators that you could use to represent anything. The two most common are an asterisk and a question mark. An asterisk indicates a match of zero or more, while a question mark represents exactly 1. For example, when used as a text string B* could be Bobby, Be, Beetle, B, B15, or anything else that begins with B. On the other hand, B?? must be three characters and begin with B, like Bee, Bob, or B11. Here are some example SUMIFS formulas with wildcards:

The following would search and sum items that are Sold with a product number beginning with 132:

=SUMIFS(A2:A22,B2:B22,"Sold",C2:C21,"132*")

Here’s what it would look like if the product code had to be exactly 5 characters:

=SUMIFS(A2:A22,B2:B22,"Sold",C2:C21,"132??")

How to Solve the Common SUMIFS Error Message

You’ll get an error message if the ranges that you’re using aren’t the same size. The SUM range must match the search ranges. Let’s use the above example again. The formula =SUMIFS(A2:A22,B2:B22,”Sold”,C2:C21,”132??”) would work because the ranges of A2:A22, B2:B22, and C2:C21, are all the same size. If we were to change the ranges to A1:A16, B2:B22, and C5:10, the formula would no longer work.

Using a Logical Operator

You can use the logical operators of NOT(), AND(), and OR(), to work with SUMIFS. These 3 logical operators are pretty self-explanatory. 

  • Use the AND() logical operator if you need to include so or more matches to sum.
  • Use the OR() logical operator if it can be any of a selection.
  • Use NOT() if you want to exclude the selected phrases.

Frequently Asked Questions

Does Google Sheets Have SUMIFS?

Yes, just type =SUMIFS( into an empty cell to start using the SUMIFS function in Google Sheets.

What Does SUMIFS Function Do in Google Sheets?

SUMIFS will sum data when more than one condition is met. For example, in a product itinerary, you could SUM product x if it has Low Stock and Not On Order in two separate columns.

How Many Criteria Can SUMIFS Have Google Sheets?

SUMIFS can have up to 127 criteria in Google Sheets.

Can You Add Two SUMIFS Together? / 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 the SUMIFS formula to pull from.

SUMIFS Sheets Points to Remember

In using the Google Sheets 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:

Most Popular Posts

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

Leave a Comment