# SUMIF Google Sheets Function [With Quiz]

**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:

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:

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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:

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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:

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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:

#### 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:

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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):

### SUMIF Dates

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

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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 :

### SUMIF Blank Or Non-Blank

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

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight 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:

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:

__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:

A | B | C | |

1 | Date | Salesperson | Sale |

2 | Jan 23 2022 | Jim Halpert | $3,000 |

3 | Jan 24 2022 | Dwight Schrute | $4,000 |

4 | Jan 25 2022 | Pam Halpert | $1,000 |

5 | Jan 26 2022 | $1,500 | |

6 | Jan 27 2022 | Stanley Hudson | $2,500 |

7 | Jan 28 2022 | Dwight Schrute | $2,500 |

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.

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:

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 :

### 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:

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:

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

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

A | B | C | D | |

1 | Salesperson | Sale | ||

2 | Jim Halpert | $3,000 | Under $2,000 | |

3 | Dwight Schrute | $4,000 | Phyllis Vance | |

4 | Pam Halpert | $1,000 | Start with 'P' | |

5 | Phyllis Vance | $1,500 | Andy & Jim | |

6 | Stanley Hudson | $2,500 | ||

7 | Phyllis Lapin | $2,500 | ||

8 | Andy Bernard | $1,800 | ||

9 | Jim Halpert | $3,000 | ||

10 | Dwight Schrute | $5,000 | ||

11 |

FREE RESOURCE

### Google Sheets Cheat Sheet

__12 exclusive tips__ to make user-friendly sheets from today:

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