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,

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.

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

### Using SUMIFS with Text Conditions

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 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 1^{st} 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.

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

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