Table of Contents

**Introduction**

As the name suggests, the SUMIFS function in Google Sheets only considers cells that meet certain specified conditions and then calculate their sum. The SUMIFS function is very handy to use in situations where you want to filter a range based on some condition and then calculate the sum based on that filter.

Consider this scenario (see image above) – Leonard and his friends are carrying various fruits. Not only are they carrying different fruits but each one is also carrying a different quantity. Leonard wants to find out the total number of Apples carried. In this example, the filtering condition is ‘Apples’ and SUMIFs function in Google Sheets is used to find the sum total of Apples carried!

SUMIFs is a better version of the SUMIF function in Google Sheets because it can handle multiple criteria.* (Optional)* You may want to explore other sum-related formulas in Google Sheets.

Instead of sum, if you wish to calculate averages on a filtered range, read our guide on AverageIfs function in Google Sheets.

**Syntax**

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

**sum_range**– Refers to the range of cells for which you want to calculate the sum.**criteria_range1**– This the range of cells against which you will check criterion1.**criterion1**– This is the condition against which the cells in criteria_range1 will be checked.**criteria_range2, criterion2, … – [ OPTIONAL ]**– These are any additional ranges and criterias that you want to check.

**Example Data**

The spreadsheet below shows a list of products purchased by various customers, along with the quantities purchased and the total bill amount. The ‘Bill status’ column indicates whether the bill has been paid or is it still pending. This information will be used to demonstrate all SUMIFS function examples.

**Basic Use **

Now let’s say for the given data I want to calculate the total quantity of purchases made by customers who have not paid the bill for their respective purchases. I will make use of the following SUMIFS Function formula to find the same.

=SUMIFS(C2:C9,E2:E9,”Pending”)

The SUMIFS Function returns the following output.

**Using SUMIFS Function with:-**

**Multiple Criteria (AND Condition)**

Let’s consider the same situation as before but this time we want to calculate the quantity of apples purchased for which the bill status is still pending. Since in this example we have to take two criteria into consideration, I have added another condition to select only **Apple **from the Product column to the previously used SUMIFS Function formula.

=SUMIFS(C2:C9,E2:E9,”Pending”,B2:B9,”Apple”)

This gives the following result.

**OR Condition**

In this case I want to calculate the quantity of apples or oranges for which the bill is still pending. Hence I will make use of two SUMIFS here. The following formula depicts this operation.

=SUMIFS(C2:C9,E2:E9,”Pending”,B2:B9,”Apple”) + SUMIFS(C2:C9,E2:E9,”Pending”,B2:B9,”Orange”)

**Greater Than/Less Than/Equal to/Not Equal to conditions **

In this example let us look at how we can calculate the total number of products corresponding which cost greater than $10.00 and have their bill status as pending.

In this case again I have to take multiple conditions into consideration while applying the SUMIFS Function. While the condition for the bill status remains same as previous examples, I add a new condition to the SUMIFS Function to select the products which have a cost greater than $10.00.

Hence, I have used the following formula to calculate the result.

=SUMIFS(C2:C9,D2:D9, “>10.00”, E2:E9, “Pending”)

As shown in the figure below, the SUMIFS Function generates the following result.

**Dynamic Values**

Let’s suppose instead of fixing the value of cost in Condition 1 of the previous example, I want to keep it dynamic. So here I will pass the condition to the function by enclosing the logical operator (>) in “ “ and concatenating it with ampersand (&).

=SUMIFS(C2:C9,D2:D9, “>”&C12, E2:E9, “Pending”)

**Empty and Non-Empty cells**

We use the following conditions to sum the cells in a column depending on whether they are empty or not-

**“=”**: Used to sum empty cells**“ ”**: Sums empty cells including zero length strings**“<>”**: Used to sum non-empty cells including zero length strings

Let’s suppose for some products the Bill Status column is empty. In such a case I want to find the price of products for which the Bill Status has been specified. I can use the following formula to do so.

=SUMIFS(D2:D9,E2:E9,”<>”)

**See Also**

Google Sheets: AVERAGEIFS Function | Easy Guide: Learn how to use the AVERAGEIFS Function in Google Sheets.

IMPORTHTML in Google Sheets: We will have a look at IMPORTHTML in Google Sheets.

## 4 responses to “Google Sheets: SUMIFS Function | Quick Guide”

[…] financial analysis – some other formulae that are frequently used are CountIF, AverageIFS and SumIFS functions. Use Amigo’s step-by-step guides to learn about each of these […]

[…] can also be done using the SUMIF function. The formula […]

[…] SUMIF function finds the sum of a particular range of numbers only if they meet particular criteria as defined by […]

[…] widely used to filter data in Google Spreadsheets and is usually combined with other functions like SUMIF, VLOOKUP, […]