Google Sheets Cheat Sheet

SUMIF Google Sheets Function [With Quiz]

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

=SUMIF(range, criterion, [sum_range])
  • 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 *.

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets 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:

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

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:

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$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.

Remember that when you want an exact match (e.g. sum of sales that are exactly $2,500) you don't need to include the text ("="):

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

All are valid and output the correct amount: $5,000.

SUMIF Text (Exact Match)

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

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

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)

You can include the equals sign for text matching if you'd like:

=SUMIF(B2:B7,"=Dwight Schrute",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:

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

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)

Although not shown here the other wildcard available to you is ?. It matches any single character (not zero or multiple characters like *).

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.

REGEXMATCH can also be case insensitive if you use the appropriate flag (?i):

=ArrayFormula(SUMIF(REGEXMATCH(B2:B7,"(?i)Halpert"),TRUE,C2:C7))

SUMIF Dates

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

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

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)

The DATE function takes in numbers for the year, month, day and outputs a date.

The DATEVALUE function takes some text and tries to recognise it as a date. It's a very powerful function but you should be aware that not all text strings will be able to be recognised as dates.

The TODAY function returns today's date.

If you're having trouble with SUMIF accurately testing dates make sure your Google Sheet's timezone is correct by checking the Settings:

SUMIF Blank Or Non-Blank

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

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

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:

=SUMIF(B2:B7,"<>",C2:C7)

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

You can (although I wouldn't) use a cell reference where the cell contains this formula ="" and is not simply blank:

=SUMIF(B2:B7,M2,C2:C7)

I do not recommend this as you're unlikely to realise at a glance that a cell contains the formula ="" and could easily accidentally delete it and ruin the output of your SUMIF.

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:

ABC
1DateSalespersonSale
2Jan 23 2022Jim Halpert$3,000
3Jan 24 2022Dwight Schrute$4,000
4Jan 25 2022Pam Halpert$1,000
5Jan 26 2022$1,500
6Jan 27 2022Stanley Hudson$2,500
7Jan 28 2022Dwight Schrute$2,500

Here's a few ways:

Add multiple SUMIF functions together

=SUMIF(B2:B7,"Jim Halpert",C2:C7)+SUMIF(B2:B7,"Dwight Schrute",C2:C7)

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

=ArrayFormula(SUM(SUMIF(B2:B7,{"Jim Halpert","Dwight Schrute"},C2:C7)))

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

=ArrayFormula(SUMIF(REGEXMATCH(B2:B7,"^Jim Halpert$|^Dwight Schrute$"),TRUE,C2:C7))

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.

To handle AND-based logic that includes multiple columns you should use the SUMIFS function.

For example: SUMIFS is the function to use if you want to know the sum of sales on or before today AND made by Dwight Shrute.

Multiple columns = SUMIFS

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:

=SUMIF($B$2:$B$7,G2,$C$2:$C$7)

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

Formula parse error.

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)

Wrong number of arguments to SUMIF. Expected between 2 and 3 arguments

Make sure you only have the following:

=SUMIF(range, criterion, [sum_range])

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.

SUMIF Function Exercises
Enter a formula for each question in the formula bar for the spreadsheet below.
1. Sales Under $2,000
In cell D2, enter a formula to find the sum of sales under $2,000. Remember to start your formula with '='.
2. Phyllis Vance's Sales
In cell D3, enter a formula to find the sum of Phyllis Vance's sales. Don't include sales from Phyllis Lapin.
3. Salespeople That Start With 'P'
In cell D4, enter a formula to find the sum of all sales from salespeople whose names start with 'P'.
4. Andy & Jim's Sales
In cell D4, enter a formula to find the sum the sales of both Jim and Andy. Please use their complete names in text strings and do not use REGEXMATCH.
ABCD
1SalespersonSale
2Jim Halpert$3,000Under $2,000
3Dwight Schrute$4,000Phyllis Vance
4Pam Halpert$1,000Start with 'P'
5Phyllis Vance$1,500Andy & Jim
6Stanley Hudson$2,500
7Phyllis Lapin$2,500
8Andy Bernard$1,800
9Jim Halpert$3,000
10Dwight Schrute$5,000
11
Click Here When Complete

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets 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.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙