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, among all the Google Sheets formulas, 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.

Table of Contents

**Become a Spreadsheet Master**

Using SUMIFS is a pretty complicated task for newer spreadsheet users. If you need help brushing up your skills first, we recommend taking a comprehensive course. Taking our course means you’ll also learn all you need to know about Google Forms too. Check out the course link below.

ACCESS GOOGLE FORMS & SHEETS MASTERCLASS

**Download Our Example Spreadsheet **

To follow along with our tutorial, you can download our SUMIFS Example Spreadsheet.

**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 Google Sheets 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,

is the range of cells containing the values you want to test.**sum_range**is the range to check for criteria1.**criteria_range1**is the condition that**criteria1***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.

Let us look at a few scenarios using this data.

**SUMIFS Google Sheets Example 1 – How to Sum With Multiple Criteria Using AND Logic**

You can also perform Google Sheet SUMIF multiple criteria. Let us say you want to f*ind 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:

**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.

**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 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_rang*e 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:

**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.

**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.

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

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.

**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 SUMIFS with Checkboxes**

Checkboxes usually stand for 2 logical expressions, which is why you can also SUMIF checkbox Google Sheets.

Let’s say we wanted to SUMIF checkboxes are checked for Manufacturing in the following example sheet.

In this case, the condition for the checked boxes is True if checked and False if unchecked.

We will use the formula:

= SUMIFS(C2:C9,B2:B9,"Manufacturing",D2:D9,"TRUE")

This formula returns the following results:

The formula has 2 conditions, the first condition is for manufacturing in column B, and the 2nd condition is for the checked boxes using the expression True.

**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 Sheet 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:**