What it does: Consider cells that meet certain specified conditions and then calculate their sum.
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 criteria that you want to check.
Sample Usage=SUMIFS(C2:C9,E2:E9,"Pending")
//Returns the sum of items from the range C2:C9 if the status of the item in E2:E9 is “Pending”.=SUMIFS(C2:C9,D2:D9, ">10", E2:E9, "Pending")
//Returns the sum of items whose price (listed in cells D2:D9) is greater than 10 with pending payment.
Introduction
As the name suggests, the SUMIFS function in Google Sheets only considers cells that meet certain specified conditions and then calculates 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.
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.