Excel SUMIF Function

Posted byPete

What does it do?

The Excel SUMIF function adds numbers together in a range when a matching evaluation is TRUE.  The evaluations can use the logical expressions i.e. GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TO <, >, <>, = for numerical criterion.  Use the asterisk * symbol and question mark ? for partial matching text strings.

Syntax Explained

Excel SUMIF function in the formula bar showing syntax.

=SUMIF(range, criteria, [sum_range])

Range: This is the range where you want to see if your matching Criteria appears in.

Criteria: This can be either a text string, number or expression and will be searched for in the Range.

Sum_range: This is the range of cell you want to sum up, if you leave it blank then the Range is used.

Examples

1. SUMIF to match a text string.

Example of an Excel SUMIF function matching one criteria.

Let’s begin with a basic version by adding the values next to each occurrence of “Red”, here we can see a 3 and 12 next to that Colour, therefore that should be 15, hey presto, it works!  You just saved a bunch of time!

2. SUMIF by using <, >, <> and =

Example of an Excel SUMIF function matching one logical operator criteria from a cell.

What if you want to find out the SUM of values but only if they are greater than, less than, not equal to and equal to?  See above, there are 4 sets of criteria, here was are referencing the logical operator (<, >, <> and =) directly from a cell with a numerical value.

It’s possible to have the logic placed into the function itself if needed/preferred, see below.

Example of an Excel SUMIF function matching one logical operator criteria directly in the formula.

3. SUMIF by partial match of a text string using the Asterisk symbol *

Example of an Excel SUMIF function partial matching a text string using the asterisk wildcard.

Sometimes you might need to SUM only if a specific word or a combination of letters appearing in the search Range, in the example above this is demonstrated by specific placement of the Asterisk * symbol.

  • Placing an asterisk at the start = The word is found at the end of the text string only.
  • Placing an asterisk at the end = The word is found at the start of the text string only.
  • Placing an asterisk at the start and end = The word appears in the text string anywhere.

4. SUMIF by wildcard using the question mark symbol ?

Example of an Excel SUMIF function partial matching a text string using the question mark wildcard.

Above you can see the results are picking up a total of 7 for row 4, this is adding DA1234 and DB1234 values.  You can add multiple question (?) marks for as many missing characters as needed.