How to use the SUMIF Function and SUMIFS Function in Microsoft Excel

Today we talk about the SUMIF function and SUMIFS function. You’ll learn how to use them and what the difference is between SUMIF and SUMIFS.

Difference Between SUMIF and SUMIFS in Excel

Located in the Math and Trig category, SUMIF and SUMIFS are essential MS Excel functions.

The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition).

The SUMIFS function returns the sum of cells that meet multiple criteria.

The criteria mentioned in both functions can be dates, numbers, and text. The function supports logical operators like >, <, <>, =, and wildcards „*,?“ for partial matching.

SUMIF function in MS Excel

Probably the most used function in MS Excel is the SUM function. Using the SUM function, you get the total SUM of the selected cells as a result.

But sometimes, we don’t need to SUM all of the cells in a row or column. Occasionally, we need a specific condition to be met in order to sum that cell. That is when we use the SUMIF formula.

The SUMIF function can be found in the Mathematical and Trig category.

The syntax for the SUMIF function is =SUMIF (range, criteria, [sum_range])

Example 1

Let’s look at an excel SUMIF example of values of different properties that were sold by an estate agent and the commissions the estate agent has gotten.

Let’s say our boss gives us a couple of questions like:

• What is the sum of the commissions for property values over $160,000?
• What is the sum of the property values over $160,000?
• What is the sum of the commissions for property values equal to $300,000?

It would be straightforward to find the answers to these questions manually, but what if you had hundreds and thousands of rows of data?

Then you’d have to use the SUMIF excel function.

 What is the sum of the commissions for property values over $160,000?

The first argument of the syntax is the range. We are checking all of the property values. The condition is that they are higher than 160000. If they are higher than 160000, sum up the commissions for those that meet the criteria.

=SUMIF(A2:A5;”>160000″;B2:B5)

The function result of $63000 can be easily manually checked. We can see that properties A3, A4, and A5 are all valued higher than 160000. That means we sum up their respective commissions at B3, B4, and B5.

14000+21000+28000= 63000

We used the SUMIF function correctly and got the correct result.

What is the sum of the property values over $160,000?

You can see straight away that only two arguments were used in this function. The range and the criteria. Let’s look at the syntax again.

=SUMIF (range, criteria, [sum_range])

When the [sum_range] is the same as the range, that’s when you don’t need to enter it. You can leave it blank.

You can, of course, use the [sum_range], the result will be correct.

So what did we do in this example? It’s very simple. We checked if the property value is higher than $160000, and if it was, we summed it up.

=SUMIF(A2:A5;”>160000″)

Property values A3, A4, and A5, are higher than 160000, and when summed up, they give the correct 900000 value.

200000+300000+400000=900000

The function was used correctly, and the result was correct, as well.

What is the sum of the commissions for property values equal to $300,000?

We are back to summing up commissions again. Now the condition is that the property value must be equal to $300000. There is only one property value that meets the condition, and the commission is 21000.

That means we used the SUMIF function correctly and have gotten the right result.

Example 2

In this example, we’ll take a look at what if the condition isn’t a number. How does MS Excel handle words as conditions?

Our boss has given us these questions:

• What is the sum of the sales of all the foods in the “Fruits” category?
• What is the sum of the sales of all the foods in the “Vegetables” category?
• What is the sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)?
• What is the sum of the sales of all foods that do not have a category specified?

What is the sum of the sales of all the foods in the “Fruits” category?

Let’s explain what we did to get the formula that can be seen on the picture

=SUMIF(A2:A7;A4;C2:C7)

For the range, we took everything in the category row from A2 to A7.

The criteria is that the food is fruit. Since fruit is mentioned in A4, we simply clicked on it. We could have also entered it manually as „Fruit“.

The [sum_range] is everything in the Sales row, from C2 to C7

The result is $2000, which is the correct result as oranges account for $800 in sales and apples are $1200. That is a total of $2000.

What is the sum of the sales of all the foods in the “Vegetables” category?

The formula =SUMIF(A2:A7;”Vegetables”;C2:C7) shows that we used a different approach to the criteria argument this time.

The range stayed the same, everything from A2 to A7.

The criterion was entered manually „Vegetables. “

The [sum_range] is, of course, all of the Sales numbers from C2 to C7.

We got the result as 12000. We know that is correct because we can manually add Tomatoes, Celery, and Carrots, and we get the sum 12000.

What is the sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)?

What can we see from the =SUMIF(B2:B7;”*es”;C2:C7) formula?

The range is the B row this time, from B2 to B7. We are checking all of the foods, not the category.

The criteria is that the food name ends with „es“. To do that we use „*“ in front of es. The „*“ represents any letters before „es“.

Just a quick example, if we wanted to sum up the values of all food that start with a C, we would enter „C*“ as the criteria.

And finally, for the [sum_range], we use everything in the Sales row from C2 to C7.

By manually adding Tomatoes, Oranges, and Apples, we know that the result of $4300 is correct and that we have used the SUMIF function correctly.

What is the sum of the sales of all foods that do not have a category specified?

=SUMIF(A2:A7;””;C2:C7)

After taking a look at the formula we used to get the correct result, only the criteria stand out.

The quotation marks „“ have no text between them. That is because we are checking the category range for no category. That means no text.

You have to write the quotation marks manually, and simply clicking on an empty cell won’t work as it counts as a zero.

We can see that we used the SUMIF function correctly again, and the result is $400 as butter is the only food without a category.

SUMIFS function in MS Excel

The SUMIFS function sums up cells that meet multiple criteria. For example, we can use SUMIFS to sum the number of retailers who (1) reside in a specific zip code and (2) whose profits exceed a particular amount.

Just like SUMIF, it supports logical operators (>,<,<>,?) and wildcards (*,?).

Example 1

If we wanted to find out the total amount of money for the red color, we’d simply use the SUMIF function because that’s only one criterion.

But what if we wanted to find the sum of all Red in the state of Texas.

Using the SUMIFS function with the criteria Red and Tx got us the result of $54.

$18+$36=$54

We used the SUMIFS function correctly. But what did we do?

Let’s take a closer look at the formula

=SUMIFS(E2:E8;B2:B8;B2;C2:C8;C2)

The Sum_range is everything in the Total($) row, which means E2 to E8.

The first criteria range is everything in the Color row, which is B2 to B8.

The first criterion is that the color is Red, so we simply clicked on B2, or we could have manually entered „Red“.

The second criteria range is everything in the State row which is C2 to C8.

And the second criterion is TX, so we clicked on C2, or we could have manually entered „TX“.

We got the result of $54, which is the correct result.

For a demonstration of how to perform a SUMIF, take a look at this hour-long class that we held on Logical Functions in Excel: