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

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 in 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 yield 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 the Excel 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 the 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 satisfy 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.

SUMIF With Or

Excel SUMIF function is basically designed with AND logic. That means a value must match ALL CONDITIONS for it to be included in the result.

Nevertheless, some techniques allow us to use OR logic instead. One method is to supply Excel SUMIF multiple criteria in an array constant (enclose the criteria list in curly braces ‘{}’) and to wrap the formula in a SUM function like this:

=SUM(SUMIF(range, {“criteria1”, ”criteria2”}, sum_range))

Let’s see an example that is used in a worksheet:

Our sample data consists of 3 variations of a product with different prices and what we want to add up are the ones that are either red or blue. As discussed previously, we can use SUMIF function wrapped inside a SUM function. So, the formula in cell F4 is this:

=SUM(SUMIF($B$2:$B$11,{“Red”,”Blue”},$C$2:$C$11))

Remember that the criteria values should be manually inputted and not as a cell reference. Text values should be enclosed in double quotation marks, and numeric values are without double quotation marks.

Note: This is not an array formula, simply hit Enter key after inputting the formula.

SUMIF Vlookup

If we want to sum the values in a dataset based on matching instances of the specified criteria, we can make a dynamic SUMIF function in Excel by combining it with the VLOOKUP function. The below sample formulas will help you understand how these Excel functions work and how to apply them to actual data.

Suppose our task is to find the total stock quantity of the product Essentials Spring – Super King. Looking at our main table, we only have product codes in our list. Thus, requiring us to search for the product name in the lookup table using its product code.

The first thing that our formula needs to do is to find all the Essentials Spring – Super King mattresses on our Main Table.

The second is to sum all the stock quantity of that product. That means we should enclose the VLOOKUP function inside the SUMIF formula in Excel just like this:

Notice that we used the VLOOKUP as the criteria argument of the Excel SUMIF function. By doing that, we have looked up the product code of Essentials Spring – Super King in our Lookup Table, and then sum up all the stock based on the matched instances of that specific product in our Main Table. Our formula returned a result of 90, equivalent to our manual count of 90 (highlighted in the previous screenshot).

The formula we used in cell B17 is:

=SUMIF($A$3:$A$12,VLOOKUP(B16,$E$3:$F$14,2,0),$C$3:$C$12)

Again, we simply hit Enter key since this is not an array formula.

Excel SUMIF Multiple Criteria

SUMIF Multiple Criteria is Excel SUMIFS

Excel SUMIFS function performs multiple conditions summing, returning the sum of cell values based on multiple criteria. 

Or simply, it is the Excel SUMIF multiple criteria or the plural form of SUMIF. However, the Excel SUMIFS function’s syntax is more complex than SUMIF.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Note that the first 3 arguments are required, additional ranges and their accompanying criteria are optional.

Just like SUMIF, SUMIFS function is designed to work with AND logic so sum range is added ONLY IF IT MEETS ALL OF THE SPECIFIED CRITERIA.

Tip: You can use up to 127 range & criteria pairs in the Excel SUMIFS formula.

How to Use SUMIFS in Excel

Let’s look at an example data and how SUMIFS function is used.

In this example, we can see that we are looking for the sum using 2 criteria – Product: Orange and Sales Rep.: John.

In order to get the total quantity of oranges John was able to sell, highlight the cell where you want the output to show and use SUMIFS function with formula in cell G3:

=SUMIFS($C$2:$C$13,$B$2:$B$13.$G$2,$A$2:$A$13,$G$2)

By analyzing that, the cells to add are from C2 to C13.  Criteria 1 range is from B2 to B13 and criteria 1 is for the cell to have the word “Orange” in it.  Criteria 2 range is from A2 to A13 and criteria 2 is for the cell to have the word “John” in it.  Thus, the formula adds rows 3, 6 and 11 in column C where it satisfies both conditions.

Note: This is not an array formula, simply hit Enter key after inputting the formula.

SUMIF Multiple Columns

Suppose the data you want to add is in multiple, different columns. There are two scenarios where we may deal with multiple columns. One is if we want to add values of the cells in multiple columns. Here, we can use the Excel SUMIF function by simply doing SUMIF ()+SUMIF()+SUMIF() and so on… Each SUMIF Excel function containing the different sum ranges that need to be added. For instance:

 

SUMIF

In our example, our purpose is to sum all sales quantity of Watermelon from Monday to Friday. Looking at our formula, we used:

=SUMIF($A$2:$A$13,$A$17,$B$2:$B$13)+SUMIF($A$2:$A$13,$A$17,$C$2:$C$13)+

SUMIF($A$2:$A$13,$A$17,$D$2:$D$13)+SUMIF($A$2:$A$13,$A$17,$E$2:$E$13)+

SUMIF($A$2:$A$13,$A$17,$F$2:$F$13)

Let’s break it down one by one for analysis.

The first SUMIF, SUMIF($A$2:$A$13,$A$17,$B$2:$B$13) refers to: range A2 to A13, criteria Watermelon in cell A17, and with sum range in column B – Monday. That means we will sum all quantities of Watermelon sales on Monday. Now, to add the all the watermelon sales on Tuesday, we will add another SUMIF function, replacing the sum range to column C like so. SUMIF($A$2:$A$13,$A$17,$C$2:$C$13). And so on, until we have included all watermelon sales across column F which is Friday.

The second scenario is where we may need to sum up values across multiple columns with multiple criteria. The solution is to combine SUMIF function with INDEX and MATCH functions. This will be the syntax:

=SUMIF(range, criteria, INDEX(array, , MATCH(lookup_value,lookup_array,[match_type])))

Let us try to use that in an Excel worksheet.

In this example, we want to add all Watermelon sales on Wednesday. Looking at our formula we can see that we have replaced the sum_range argument with INDEX & MATCH functions. This way, we are horizontally searching for our second criteria which is Wednesday across multiple columns. And so, we have a result of 35. Manually counting, we also get the value of 35.

Note: This is not an array formula, simply hit Enter key after inputting the formula.

SUMPRODUCT for Dealing With Excel SUMIF Multiple Criteria

In case our multiple criteria are listed in the worksheet instead of manually inputted in the formula, we can use SUMIF and SUMPRODUCT functions together. The SUMPRODUCT function multiplies the components in the given arrays and returns the sum of the items. Below is a sample formula used in the worksheet:

Formula used in cell B19:

=SUMPRODUCT(SUMIF($A$2:$A$13,$A$17:$A$18,$B$2:$B$13))

Alternatively, you can still list the criteria directly into the formula enclosed in curly braces like so:

=SUMPRODUCT(SUMIF($A$2:$A$13{“Watermelon”, “Apple”},$B$2:$B$13))

Both methods will be returning same results.

Note: This is not an array formula, simply hit Enter key after inputting the formula.

Troubleshooting

Array Arguments to SUMIFS are of Different Size

When using SUMIFS function always make sure that the sum range and all criteria ranges are the same size and orientation. In other words, if the sum range column is in a column from row 1 to 100, then the criteria range MUST be in a column with the same number of rows as well.

Look at the illustrations below:

See that #VALUE! error? Look at the picture below with highlighted formula arguments. Notice that the first argument range is from row 2 to 11, but the sum_range is from row 3 to 11.

Let’s evaluate the formula:

=SUMIFS($C$3:$C$11,$B$2:$B$11,”>=”&B13,$B$2:$B$11,”<=”&B14)

$C$3:$C$11 – sum_range  (row 3 to 11)

$B$2:$B$11 – criteria_range1 (row 2 to 11)

This is what caused the #VALUE! error in cell B16.

Again, always keep in mind when using SUMIFS: The sum_range and criteria ranges are required to be same in size and play Big Dollar casino.