SUMIF - Sum Values Based on Criteria in Excel

The SUMIF function allows you to sum values based on a single criteria. This function works in all versions of Excel.

If you need to sum on more than 1 criteria, take a look at the SUMIFS function (note the S at the end of it).

Sections:

Syntax

Example 1 - Text

Example 2 - Numbers

Example 3 - Operators

Criteria Comparison Operators

Wildcards

Notes

Syntax

=SUMIF(range, criteria, [sum_range])

[] means the argument is optional.

Example 1 - Text

Let's sum all values that are for "west" in a data table.

=SUMIF(A1:A5,"west",B1:B5)

Result:

This is a very simple example that matches text. Remember to enclose text criteria with double quotation marks.

Example 2 - Numbers

Let's sum all values that equal 10. Now this is a rather silly example given my current data set, but it illustrates a couple things, mentioned below.

=SUMIF(B1:B5,10)

Result:

Notice that the third argument is not needed since the range against which to check the criteria is the same as the range to sum.

Also notice that there are no quotation marks around the number for the criteria argument. In the next example, the number will need quotation marks because it will be using a comparison operator, but here that is not the case.

In this example, the result is 10 because there is only one 10; however, if you change another number to 10, the result will be 20, the sum of both numbers that equal 10.

Example 3 - Operators

Operators allow you to sum values that are greater than, less than, or not equal to a value.

Let's sum all numbers greater than 10.

=SUMIF(B1:B5,">10")

Result:

">10" is the criteria argument. You can see the greater than sign that is paced before the number but within the double quotation marks.

It is very important that criteria operators are placed within the quotation marks!

Below, you will find an explanation of criteria operators and also wildcards. These help you make more sophisticated SUMIF functions.

Criteria Comparison Operators

You don't just have to check if a criterion is equal to a value; you can check if the criterion is greater than or less than a value or not equal to a value and more.

Here is a list of the comparison operators that you can use:

> Greater than. Means the values must be greater than whatever you put after this sign. This was used in the above example.

< Less than. Means the values must be less than whatever number you put after this sign.

>= Greater than or equal to. Means the values must be equal to or greater than the number that you put after this sign.

<= Less than or equal to. Means the values must be equal to or less than the number that you put after this sign.

<> Not equal to. This works for both numbers and text and says that the value must not be equal to whatever you put after this sign.

Note: it is very important that you put these signs inside of double quotation marks, even when you use them with a number (illustrated in the example above).

To learn more, check out our tutorial on comparison operators in Excel.

Wildcards

You can also use what are called wildcards in this function.

? Question mark. Matches any character. So, "Th?n" would match "Then" "Than" "Th8n" etc.

* Asterisk. Matches any number of any characters that come before, after, or in the middle of a value. So, "*hi*" would match "oh hi there" "hi there" "oh hi" etc. As you can see, you can put the asterisk before and after the value, but you can also put it only before the value if you want to match everything that comes before the value or after it to match everything that comes after it.

~ Tilde. This allows you to literally match a ? or a * character. To match a question mark, do this "~?" and to match an asterisk do this "~*" and to match a tilde with a question mark or with an asterisk do this "~~?" or this "~~*".

Wildcards can be confusing at first and they are not often used in Excel but they can be very helpful.

To learn more, check out our tutorial on wildcards in Excel.

Notes

The SUMIF function is a very helpful function that allows you to perform quick filters on data in order to sum only what you want to sum.

If you need to have more than 1 criterion for the SUMIF function, use the SUMIFS function (note the S at the end of it).

Make sure to download the file for this tutorial so you can work with these examples in Excel.