**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 (1^{st} 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.

=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; 1^{st} is manually typing the text criteria in the formula and the 2^{nd} is to use cell reference. Again, 2^{nd} 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 1^{st} 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.