SUMIF Google Sheets Function [With Quiz]

Kieran Dixon Updated:  January 26, 2022

The SUMIF function in Google Sheets SUMs all of the values in a range only IF they meet specific criteria.

It helps with things like finding the sum of:

• sales by a specific salesperson
• costs of a specific department
• the search volume of keywords that contain a specific word

SUMIF Syntax

• range = a range reference (or array of values) to check against the provided criterion and sum (if there is no separate [sum_range]).
• criterion = pattern or logic test to check the range:
  • Equal to: 1, "1", "=1", "text", or "=text"
  • Not equal to: "<>1" or "<>text"
  • Greater than: ">1"
  • Greater than or equal to: ">=1"
  • Less than: "<1"
  • Less than or equal to: "<=1"
• [sum_range] = [optional] a range reference (or array of values) to sum if different from range. This range should always contain the numbers you want to sum.

When the criterion tests text data you can include wildcards:

• ? to match any single character
• * to match zero or more adjacent characters

To include an actual ? or * in your criterion, put the tilde (~) before the ? or *.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

SUMIF Function Examples

Here's a table of sales data with dates, salespeople (one of which is missing), and sale size:

You're going to learn how to analyze this data in many different ways using SUMIF:

SUMIF Numbers

You want to know the sum of sales when the individual sale amount is more than or equal to than $2,500:

Here are a few SUMIF formulas (all are correct) where the criteria is:

• Just text: =SUMIF(C2:C7,">=2500")
• Text and a number: =SUMIF(C2:C7,">="&2500)
• Text and a cell reference (E2 contains 2500): =SUMIF(C2:C7,">="&E2)

All three formulas are valid and output the correct amount: $12,000.

SUMIF Text (Exact Match)

You want to know the sum of sales made by Dwight Schrute:

Here are two formulas that get you the correct result of $6,500:

• Text: =SUMIF(B2:B7,"Dwight Schrute",C2:C7)
• Cell reference ("Dwight Schrute" is in G2): =SUMIF(B2:B7,G2,C2:C7)

Making SUMIF Exact Match Case Sensitive

SUMIF text matching is case insensitive.

It doesn't distinguish between upper and lowercase letters.

These formulas are just as valid as the ones above:

• =SUMIF(B2:B7,"DWIGHT SCHRUTE",C2:C7)
• =SUMIF(B2:B7,"dwight schrute",C2:C7)

If you need your formula to be case sensitive, you can use variations of:

• =ArrayFormula(SUMIF(FIND("Dwight Schrute",B2:B7),">=0",C2:C7))
• =ArrayFormula(SUMIF(REGEXMATCH(B2:B7,"Dwight Schrute"),TRUE,C2:C7))

Both FIND and REGEXMATCH are case sensitive and the range to check against the provided criterion is the output of these functions.

SUMIF Text With Wildcards (Partial Match)

You want to know the sum of sales made by the Halperts:

Here are two formulas that get you the correct result ($4,000):

• Text: =SUMIF(B2:B7,"*Halpert",C2:C7)
• Cell reference ("Halpert" is in I2): =SUMIF(B2:B7,"*"&I2,C2:C7)

Making SUMIF Partial Match Case Sensitive

As SUMIF ignores case you need to get creative if you want things to be case sensitive:

• =ArrayFormula(SUMIF(FIND("Halpert",B2:B7),">=0",C2:C7))
• =ArrayFormula(SUMIF(REGEXMATCH(B2:B7,"Halpert"),TRUE,C2:C7))

On top of this, using REGEXMATCH within SUMIF allows you to develop more complex matching patterns than the wildcards built into SUMIF.

SUMIF Dates

You want to know the sum of sales made on or before today:

Here are a few formulas to get you the correct result ($9,500):

• DATE function: =SUMIF(A2:A7,"<="&DATE(2022,1,26),C2:C7)
• DATEVALUE function: =SUMIF(A2:A7,"<="&DATEVALUE("26 Jan 2022"),C2:C7)
• TODAY function: =SUMIF(A2:A7,"<="&TODAY(),C2:C7)
• Cell reference (today's date is in K2): =SUMIF(A2:A7,"<="&K2,C2:C7)

SUMIF Blank Or Non-Blank

You want to know the sum of sales made by the missing salesperson or by entered salespeople:

For the missing salesperson (blank cells), these formulas will do the trick:

• Text: =SUMIF(B2:B7,"",C2:C7)
• Text: =SUMIF(B2:B7,"=",C2:C7)

In the second text-based formula the equals sign ("=") is ignored and assumed to mean a search for equality to nothing rather than the character itself.

For entered salespeople (non-blank cells), this formulas will work:

With "<>" being the operator for 'not equal to'.

SUMIF Multiple Criteria

SUMIF can handle OR-based logic that includes one column if you know how to make it happen.

If you want to know the sum of sales by both Jim Halpert and Dwight Schrute:

Here's a few ways:

Add multiple SUMIF functions together

This is my least favorite method because if you have a lot of criteria it gets painful to update if range changes need to be made manually.

Sum an array of SUMIF outputs

This formula is far more succinct and easier to update.

Simply add a comma-separated name to the text array and you're off! No need to update every range in the formula if something changes.

REGEXMATCH

This is also quite nice but requires an understanding of regular expressions that others who come along to edit your formula might not have.

Use it only if appropriate.

SUMIF Errors

Your SUMIF formula is not working…

Output Is Always Zero

Possible causes include:

• Nothing matches your criterion. Double check it's correct.
• The [sum_range] you're referencing might not contain numbers so there's nothing to sum

Case Sensitivity

Remember that SUMIF is not case sensitive.

If you need your formula to be case sensitive try the tips and hints for exact and partial text matches.

Absolute Referencing

Use absolute references when using the autofill handle or copying and pasting formulas:

Otherwise the newly placed formulas might reference the wrong ranges.

Date Criteria Not Accurate

Dates in Google Sheets are heavily-dependent on the timezone of each individual sheet.

If you're struggling with date-based criteria within SUMIF, make sure your sheet's timezone is correct by double-checking the Timezone in the Settings:

Error Messages

This can be for a number of reasons but it's likely to be a simple syntax error.

When combining text and cell references you need to make sure to include the ampersand operator (&) to concatenate them properly:

• Error: =SUMIF(C2:C7,">="E2)
• No error: =SUMIF(C2:C7,">="&E2)

Make sure you only have the following:

Go through and check that all of your nested function parentheses are opening and closing in the right spots to ensure you've only got 2-3 arguments.

SUMIF Function Quiz

Here's a quick summary of how to use the quiz spreadsheet:

1. Select the required cell while the formula bar is empty
2. Type your answer in the formula bar
3. You can click on the spreadsheet to add cell/range references

Complete all questions to solidify your understanding of the SUMIF function.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

PROPER Google Sheets Function [With Quiz]

The PROPER function in Google Sheets is a powerful way to capitalize words. Be careful though, there might be some unintended consequences.

TRIM Google Sheets Function [With Quiz]

The TRIM function in Google Sheets cleans text for you! Leading, trailing, and unnecessary spaces are removed so you get only validated text.

COUNT Google Sheets Function [With Quiz]

The COUNT function in Google Sheets tells you how many numbers are present in a dataset, ignoring everything else. Learn how to count here.