SUMIF - Sum Values Based on Criteria in Excel

Add to Favorites
Author:

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])
Argument Description
Range This can be both the range against which the criteria is checked and the range that contains the values to sum or, if you use the sum_range argument, then this argument is only used to check criteria.
Criteria

The criteria that is used to determine which values to add together. This criterion is checked against the range in the range argument.

If you use text or numbers with operators, you must enclose them with double quotation marks, as shown in the below example.

To view all possible criteria operators and wildcards, go to the Criteria Comparison Operators and Wildcards sections lower in this tutorial.

[Sum_range]

Optional. This tells the function which range of values to sum. Use this argument when the range of values to sum is different from the range in the range argument.

[] 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)

241c241ba4f614e85a0f09e106599e63.png

Result:

4dc00204dce8794ca43e4aec03bfae2b.png

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)

077cea3699a48b6396605dc0a3e27de5.png

Result:

b97410b76493c523436dcbbeeffaa934.png

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")

3369ac855a4c193bca89fdd5ba13651c.png

Result:

43e192dd3ac967404a8c3f79b9aa2318.png

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


Excel Function: SUMIF()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: SUMIF()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course