Excel SUMIF Function: Ultimate Guide

In this guide you are going to learn all about the Excel SUMIF function which is the most important Excel formula there is for summarizing your information (and also SUMIFS which will be tackled later).

Now the SUMIF is all about conditional summing, that is we only sum all numbers in a range based on one criterion. Aside from numbers, SUMIF can also add cells based on dates and text that match specific criteria. Logical operators can be utilized such as <, >, = and wildcards (*,?) for partial matching.

How to use SUMIF Function

SUMIF function’s syntax is:

=SUMIF(range, criteria, [sum_range])

Range – this is the range of cells that you want to apply the criteria against

Criteria – the criteria used to determine which cells to add

Sum_range – Optional, this is the range of cells to sum together. However, it uses the Range (1st argument) as the sum_range if this parameter is omitted.

Let us explore how to use the Excel SUMIF function:

Suppose we have a list of the quantity of our product (in this case, ribbon) and we want to sum all based on their colors. Below, you can see our data, formulas, and the results returned by our SUMIF formula.

=SUMIF($B$2:$B$11:E2,$C$2:$C$11)

Let’s evaluate our sample starting with the Red ribbons. By manual verification, we can see that we have 2 entries of Red with quantities 16 and 2, which sums up to 18. Next is 3 entries of Blue with quantities 12, 6 and 22. That’s a total of 40. Again, same results returned by the SUMIF formula.

SUMIF not equal

SUMIF not equal, by using logical operators “<>” returns the sum of all cells that are NOT EQUAL TO THE SUPPLIED CRITERIA. Here’s a quick example to show how to use it.

Formula in cell F2:

=SUMIF($B$2:$B$11,”<>”&2000,$C$2:$C$11)

Formula in cell F3:

=SUMIF($B$2:$B$11,”<>”&E3,C$2:$C$11)

In our example, we want to get the sum of the persons not born on the year 2000. As you can see, we can use the formula in 2 ways, one is to manually include the criteria (2000) in the formula by typing it in and the other is to use cell reference as shown in the second formula. Both yields the same result (53), but I personally prefer the latter since it’s dynamic and gives convenience when changing the criteria from one to another.

Another scenario is using text in the ‘not equal to’ criteria. Below is an example:

Formula in cell F2:

=SUMIF($B$2:$B$11,”<>”&”Red”,$C$2:$C$11)

Formula in cell F3:

=SUMIF($B$2:$B$11,”<>”&E3,$C$2:$C$11)

Same as our first example, we can use the formula in 2 ways; 1st is manually typing the text criteria in the formula and the 2nd is to use cell reference. Again, 2nd formula is preferable due to its convenience when changing the criteria from one to another.

Note that the text in criteria is not case sensitive.

SUMIF date range

To sum cell values based on dates, you can use SUMIF function together with logical operators <,>, =>, etc. in your criteria. You can refer to the examples below for better comprehension.

Formula in cells F2 and F6:

=SUMIF($B$2:$B$11,”>”&E2,$C$2:$C$11)

=SUMIF($B$2:$B$11,”>”&DATE(2000,5,12),$C$2:$C$11)

Formula in cells F3 and F7:

=SUMIF($B$2:$B$11,”<”&E2,$C$2:$C$11)

=SUMIF($B$2:$B$11,”<”&DATE(2000,5,12),$C$2:$C$11)

Formula in cells F4 and F8:

=SUMIF($B$2:$B$11,”=”&E3,$C$2:$C$11)

=SUMIF($B$2:$B$11,”=”&DATE(2000,5,12),$C$2:$C$11)

Note that ‘=’ operator can be omitted for ‘is equal’ comparison. It will also yield same result.

By looking at the example, there are 2 ways to use SUMIF function on date ranges. One is by cell referencing and the other is by using the DATE function to create a date.

The DATE function is a safe way to use on function criteria, since it creates a valid date from individual year, month, and day components. This eradicates problems related to regional date settings.

 

SUMIF greater than

To sum cell values that are greater than a given value (criteria), use SUMIF function with “>” at the beginning of criteria argument. You can use it to compare either a number or a text. Here are some examples.

Formula in cell G2:

=SUMIF($C$2:$C$11,”>”&F2,$D$2:$D$11)

Formula in cell G3:

=SUMIF($C$2:$C$11,”>”&F3,$D$2:$D$11)

Formula in cell G4:

=SUMIF($C$2:$C$11,”>”&F4,$D$2:$D$11)

As discussed in the introduction of this article, we can use SUMIF and logical operators to sum cell values that satisfies the comparison criteria, which, in this case is the greater than ‘>’ operator. Looking at our 1st formula, it sums up all the quantities with prices that are >1,300, and that is the item with product code MD00926 with quantity 35, same as what our formula returned.