The SUMIF function in Google Sheets is a really useful tool for adding up values based on a single condition. Basically, it goes through a range of cells, checks for the condition you provide, and sums up only those values that match.
For instance, imagine you’ve got a list of expenses and you only need to total the ones that are over a certain amount. Or maybe you have a list of items with their prices, and you want to quickly find the total cost of a specific item. That’s where SUMIF shines.
SUMIF can handle numbers, text, dates, wildcards, and even blank or non-blank cells as criteria. Google Sheets also offers SUMIFS, which is great for summing numbers based on multiple conditions. SUMIF handles one condition while SUMIFS tackles multiple conditions.
Here, we’ll explore how to use both SUMIF and SUMIFS to add up numbers based on specific criteria.
SUMIF Function: How it Works
The SUMIF function combines the power of SUM and IF functions. IF checks the range for your condition, and then SUM adds up the corresponding numbers that meet that condition.
Syntax of SUMIF Function:
The general structure of the SUMIF function is:
=SUMIF(range, criteria, [sum_range])
Arguments Explained:
- range - This is the range of cells that will be checked against your condition.
- criteria - This is the condition that determines which cells get added. It can be a number, text string, date, cell reference, expression, logical operator, wildcard, or even another function.
- sum_range - This is an optional range of cells containing values to sum when the criteria is met in the range. If you skip it, SUMIF will sum the ‘range’ itself.
Let’s see it in action using different types of criteria.
SUMIF with Number Conditions
You can use comparison operators to set number conditions in your SUMIF criteria.
Here are the operators you can use:
- greater than (>)
- less than (<)
- greater than or equal to (>=)
- less than or equal to (<=)
- equal to (=)
- not equal to (<>)
Let’s say you have a spreadsheet and you want to calculate the total sales for amounts equal or greater than 1000.
Here’s how you’d do that with SUMIF:
First, choose the cell where you want the result (e.g., D3). Then, to sum numbers in B2:B12 that are 1000 or greater, enter this formula and hit ‘Enter’:
=SUMIF(B2:B12,">=1000",B2:B12)
In this formula, B2:B12 is both the range and the sum_range because we’re summing the values where the criteria is applied. The number is enclosed in quotes because all criteria should be in double quotes.
This formula looks for the numbers greater than or equal to 1000 and then sums the matching values, showing the total in D3.
Since range and sum_range are the same, you can simplify this formula by excluding sum_range:
=SUMIF(B2:B12,">=1000")
You can also refer to a cell that contains your number criteria instead of typing it directly. In that case, the comparison operator must be in quotes and joined with the cell reference by an ampersand (&):
=SUMIF(B2:B12,">="&D2)
Important: When using a cell reference, make sure there aren’t any extra spaces in the cell. If there are any leading or trailing spaces, the formula will return zero.
You can use other logical operators the same way. For example, to sum values less than 500:
=SUMIF(B2:B12,"<500")
Sum If Numbers Equal To
To add numbers equal to a specific value, you can either enter the number directly or with the equal sign (=) in the criteria.
For example, let’s sum sales amounts in column B for quantities in column C that are exactly 20. Here are some ways you can do that:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
To sum numbers in column B where the quantity in C is not equal to 20, use:
=SUMIF(C2:C12,"<>20",B2:B12)
SUMIF with Text Conditions
To sum values when a cell in your range contains specific text, just put that text in the criteria argument. Remember that text strings should always be enclosed in double quotes (" ").
If you need the total sales from the ‘West’ region, use this:
=SUMIF(C2:C13,"West",B2:B13)
This searches for “West” in the range C2:C13 and sums the corresponding sales from column B.
You can also use a cell reference that contains the text in criteria:
=SUMIF(C2:C12,E2,B2:B12)
To calculate the total revenue excluding the ‘West’ region, use the “not equal to” operator (<>):
=SUMIF(C2:C12,"<>"&E2,B2:B12)
SUMIF with Wildcards
Sometimes, you need to sum values based on partial text matches. That’s where wildcards come in.
Here are the wildcards you can use:
?(question mark): Matches any single character.*(asterisk): Matches any sequence of characters.~(tilde): Used to match a literal ? or * character.
We’ll use this data example for the wildcards:
Asterisk (*) Wildcard
To sum quantities of all Apple products, use:
=SUMIF(A2:A14,"Apple*",B2:B14)
This finds all products starting with “Apple” and sums their quantities.
You can use multiple wildcards and cell references with wildcards:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)
This formula sums quantities of all products containing ‘Redmi’ anywhere in the text.
Question Mark (?) Wildcard
You can use the question mark to match any single character.
To find quantities of all Xiaomi Redmi 9 variants, try this:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)
This looks for text strings with “Xiaomi Redmi 9” followed by any single character and sums the corresponding quantities.
Tilde (~) Wildcard
To match an actual question mark (?) or asterisk (*), use the tilde (~) before the wildcard.
To add quantities with the asterisk at the end, use:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
To add quantities where the text includes a question mark, use:
=SUMIF(A2:A14,"~?",B2:B14)
SUMIF with Date Conditions
You can also use SUMIF with date criteria, like summing numbers for a specific date, before a date, or after a date. You can use comparison operators with the date criteria. Dates need to be in a recognized Google Sheets format, a cell reference containing a date, or using functions like DATE() or TODAY().
Here is an example dataset:
To sum sales on or before November 29, 2019, you can use:
=SUMIF(C2:C13,"<=November 29, 2019",B2:B13)
The formula matches cells with dates on or before November 29, 2019, and sums the corresponding sales.
You can use any date format Google Sheets recognizes (like ‘November 29, 2019’, ‘29 Nov 2019’, or ‘29/11/2019’). Make sure the date and operator are in double quotes.
Or you can use the DATE() function:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Or a cell reference that contains the date:
=SUMIF(C2:C13,"<="&E2,B2:B13)
To sum sales for today’s date, use the TODAY() function:
=SUMIF(C2:C13,TODAY(),B2:B13)
SUMIF for Blank and Non-Blank Cells
Sometimes, you might need to sum based on whether cells are blank or not. You can use SUMIF to do that as well.
Sum if Blank
You can use "" or = to represent blank cells.
To sum sales amounts with empty strings (visually blank) in column C, use:
=SUMIF(C2:C13,"",B2:B13)
To sum sales with completely blank cells in column C, use “=”:
=SUMIF(C2:C13,"=",B2:B13)
Sum if Not Blank
To sum sales where the cell isn’t empty, use “<>”:
To get the total sales amount with any dates entered, use:
=SUMIF(C2:C13,"<>",B2:B13)
SUMIF with Multiple Criteria (OR Logic)
While SUMIF is designed for a single condition, you can combine multiple SUMIF functions to sum values based on multiple criteria with OR logic.
For instance, to sum sales in the ‘West’ or ‘South’ regions, use:
=SUMIF(C2:C13,"West",B2:B13)+SUMIF(C2:C13,"South",B2:B13)
This adds up sales where either of the conditions are true. This will also sum when all conditions are met.
If only one condition is met, it will only return that sum.
You can use more than two criteria as well. Using cell references is helpful if you have a lot of criteria:
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
If you want to sum values only when all conditions are met, you should use the SUMIFS function.
SUMIFS Function (Multiple Criteria)
When you need to sum values based on multiple conditions and you want them all to be true, SUMIFS is your function. It’s like SUMIF, but better.
SUMIFS Function Syntax and Arguments
The syntax of the SUMIFS function is:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])
Where:
- sum_range - The range of cells to sum when all conditions are met.
- criteria_range1 - The first range to check against
criteria1. - criteria1 - The first condition to check against
criteria_range1. - criteria_range2, criterion2, … - Any additional ranges and criteria to evaluate.
Here is sample data for SUMIFS:
SUMIFS with Text Conditions
You can sum values based on different text criteria in different ranges. To find the total sales amount of delivered tents, use:
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"Delivered")
This checks for the item “Tent” in A2:A13 and “Delivered” in C2:C13 and sums the corresponding amount in D2:D13 only when both are true.
SUMIFS with Number Criteria and Logical Operators
You can use logical operators with numbers in your conditions.
To find the total sales of more than 5 of any item in California (CA):
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")
This formula checks for quantities greater than 5 in D2:D13 and state as ‘CA’ in B2:B13 and sums the sales in E2:E13 if both are met.
SUMIFS with Date Criteria
You can also check multiple conditions in the same range or different ranges with dates.
For example, to get total sales of delivered items after 5/31/2021 and before 6/10/2021 use:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)
Here, we’re checking for dates after 5/31/2021 (G1) and before 6/10/2021 (G2) in D2:D13, and for a “Delivered” status (G3). And when all conditions are true, it sums the value from E2:E13.
SUMIFS with Blank and Non-Blank Cells
You can also use "=", "", and "<>" to check for blank and non-blank cells.
To sum the amount of “Tent” items where the delivery date hasn’t been confirmed (blank cells):
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"=")
This will find ‘Tent’ items where the corresponding cell in column C is completely blank and sums the value in D.
To find the sum of ‘Tent’ items where the delivery date has been confirmed (not empty cells), use:
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"<>")
This formula is just like the previous one but uses “<>” which checks for non-blank cells.
SUMIFS with OR Logic
Since SUMIFS works on AND logic, you can use multiple SUMIFS functions to use OR logic.
For example, to add up sales for either ‘Bike rack’ or ‘Backpack’ with the status ‘Ordered’, you can use:
=SUMIFS(D2:D13,A2:A13,"Bike rack",C2:C13,"Ordered")
+SUMIFS(D2:D13,A2:A13,"Backpack",C2:C13,"Ordered")
This formula checks two conditions “Bike rack” and “Ordered” and sums the amount. Then, it checks for “Backpack” and “Ordered” and sums the amount. Finally, it adds both sums.
That’s everything you need to know about using SUMIF and SUMIFS!