Sometimes, you may want to add up only the values in a range that satisfy a specific criterion, such as being greater than a certain number, equal to a text string, or matching a date. For example, you may want to calculate the total sales for a particular product, region, or month. In this article, you will learn how to use the SUMIF function in Excel to perform this task. You will also see some examples of how to use SUMIF with multiple criteria, wildcards, and dates.
The SUMIF function in Excel allows you to sum the values in a range that meet a single condition that you specify. The syntax of the SUMIF function is:
=SUMIF (range, criteria, [sum_range])
where:
- range is the range of cells that you want to evaluate by the criteria. This argument is required.
- criteria is the condition that must be met for a cell to be included in the sum. This argument is required. It can be a number, text, date, logical expression, cell reference, or another Excel function. For example, you can enter criteria such as “5”, “apples”, “10/25/2021”, “>5”, etc. Note that any text criteria or criteria containing logical operators must be enclosed in double quotation marks, e.g. “apples”, “>10”.
- sum_range is the range of cells that you want to sum if the condition is met. This argument is optional. If you omit it, Excel will sum the values in the range argument. Note that the sum_range must have the same size and shape as the range argument.
Procedures
To use the SUMIF function in Excel, follow these steps:
- Select the cell where you want to enter the formula.
- Type =SUMIF ( and then select the range of cells that you want to evaluate by the criteria, or type the range address. For example, =SUMIF (A2:A10,
- Type a comma (,) and then enter the criteria that you want to apply, or select the cell that contains the criteria. For example, =SUMIF (A2:A10, “North”,
- Type another comma (,) and then select the range of cells that you want to sum if the condition is met, or type the range address. If you want to sum the same range as the first argument, you can skip this step. For example, =SUMIF (A2:A10, “North”, C2:C10
- Type a closing parenthesis ) and then press Enter. Excel will calculate the sum of the values that meet the criteria and display the result in the selected cell. For example, =SUMIF (A2:A10, “North”, C2:C10) will return the sum of the sales amounts in column C for the region “North” in column A.
Explanation
To better understand how the SUMIF function works, let’s look at some examples with different types of criteria and sum ranges.
Example 1: Sum if greater than or less than
Suppose you have a table that shows the sales figures for different products and regions, as shown below:
| Product | Region | Sales |
|---|---|---|
| A | North | 500 |
| B | South | 400 |
| C | East | 300 |
| D | West | 200 |
| E | North | 600 |
| F | South | 700 |
| G | East | 800 |
| H | West | 900 |
You want to find the total sales for the products that have sales greater than 500. To do this, you can use the following SUMIF formula:
=SUMIF (C2:C9, “>500”)
This formula will evaluate the values in the range C2:C9 and sum only those that are greater than 500. The result is 3000, which is the sum of 600, 700, 800, and 900.
You can also use the SUMIF function to sum values that are less than a certain number. For example, to find the total sales for the products that have sales less than 500, you can use this formula:
=SUMIF (C2:C9, “<500”)
This formula will return 900, which is the sum of 400, 300, and 200.
Example 2: Sum if equal to
You can also use the SUMIF function to sum values that are equal to a specific number, text, or date. For example, to find the total sales for the product A, you can use this formula:
=SUMIF (A2:A9, “A”, C2:C9)
This formula will evaluate the values in the range A2:A9 and sum the corresponding values in the range C2:C9 if they are equal to “A”. The result is 500, which is the sales amount for product A.
You can also use a cell reference instead of typing the criteria directly in the formula. For example, if you have the product name in cell F1, you can use this formula:
=SUMIF (A2:A9, F1, C2:C9)
This formula will return the same result as the previous one, but it will update automatically if you change the value in cell F1.
Example 3: Sum if not equal to
You can also use the SUMIF function to sum values that are not equal to a specific number, text, or date. To do this, you need to use the not equal to operator (<>) in the criteria argument. For example, to find the total sales for the products that are not A, you can use this formula:
=SUMIF (A2:A9, “<>A”, C2:C9)
This formula will evaluate the values in the range A2:A9 and sum the corresponding values in the range C2:C9 if they are not equal to “A”. The result is 4500, which is the sum of all the sales amounts except for product A.
Example 4: Sum if blank
You can also use the SUMIF function to sum values that are blank or empty. To do this, you need to use an empty string (“”) in the criteria argument. For example, suppose you have a table that shows the sales figures for different products and regions, but some cells are blank, as shown below:
| Product | Region | Sales |
|---|---|---|
| A | North | 500 |
| B | South | |
| C | East | 300 |
| D | West | 200 |
| E | North | |
| F | South | 700 |
| G | East | 800 |
| H | West | 900 |
You want to find the total sales for the regions that have blank sales values. To do this, you can use the following SUMIF formula:
=SUMIF (C2:C9, “”, B2:B9)
This formula will evaluate the values in the range C2:C9 and sum the corresponding values in the range B2:B9 if they are blank. The result is 2, which is the count of the regions that have blank sales values (South and North).
Example 5: Sum if not blank
You can also use the SUMIF function to sum values that are not blank or empty. To do this, you need to use the not equal to operator (<>) with an empty string (“”) in the criteria argument. For example, to find the total sales for the regions that have non-blank sales values, you can use this formula:
=SUMIF (C2:C9, “<>”, B2:B9)
This formula will evaluate the values in the range C2:C9 and sum the corresponding values in the range B2:B9 if they are not blank. The result is 6, which is the count of the regions that have non-blank sales values (East, West, North, and South).
Example 6: SUMIF examples with wildcard characters
You can also use the SUMIF function to sum values that match a partial text string or a pattern. To do this, you need to use wildcard characters in the criteria argument. There are two wildcard characters that you can use in Excel:
- The asterisk () matches any sequence of characters. For example, “A” matches any text that begins with “A”.
- The question mark (?) matches any single character. For example, “A?” matches any two-character text that begins with “A”.
For example, suppose you have a table that shows the sales figures for different products and regions, as shown below:
| Product | Region | Sales |
|---|---|---|
| A1 | North | 500 |
| B2 | South | 400 |
| C3 | East | 300 |
| D4 | West | 200 |
| E5 | North | 600 |
| F6 | South | 700 |
| G7 | East | 800 |
| H8 | West | 900 |
You want to find the total sales for the products that have a number in their name. To do this, you can use the following SUMIF formula:
=SUMIF (A2:A9, “*?”, C2:C9)
This formula will evaluate the values in the range A2:A9 and sum the corresponding values in the range C2:C9 if they match the pattern “*?”. This means that any text that ends with a single character will be matched. The result is 4400, which is the sum of the sales amounts for products A1, B2, C3, D4, E5, F6, G7, and H8.
You can also use the question mark wildcard to match a specific position in the text. For example, to find the total sales for the products that have a number 5 in their name, you can use this formula:
=SUMIF (A2:A9, “?5”, C2:C9)
This formula will evaluate the values in the range A2:A9 and sum the corresponding values in the range C2:C9 if they match the pattern “?5”. This means that any text that has a 5 as the second character will be matched. The result is 600, which is the sales amount for product E5.
You can also combine the asterisk and the question mark wildcards to create more complex patterns. For example, to find the total sales for the products that have a letter followed by a number in their name, you can use this formula:
=SUMIF (A2:A9, “?*”, C2:C9)
This formula will evaluate the values in the range A2:A9 and sum the corresponding values in the range C2:C9 if they match the pattern “?*”. This means that any text that has a letter as the first character and any sequence of characters after that will be matched. The result is 4400, which is the same as the previous example.
Example 7: SUMIF examples with dates
You can also use the SUMIF function to sum values that match a specific date or a date range. For example, suppose you have a table that shows the sales figures for different products and dates, as shown below:
| Product | Date | Sales |
|---|---|---|
| A | 01/01/2024 | 500 |
| B | 01/02/2024 | 400 |
| C | 01/03/2024 | 300 |
| D | 01/04/2024 | 200 |
| E | 01/05/2024 | 600 |
| F | 01/06/2024 | 700 |
| G | 01/07/2024 | 800 |
| H | 01/08/2024 | 900 |
You want to find the total sales for the month of January 2024. To do this, you can use the following SUMIF formula:
=SUMIF (B2:B9, “01/2024”, C2:C9)
This formula will evaluate the values in the range B2:B9 and sum the corresponding values in the range C2:C9 if they match the date “01/2024”. This means that any date that has the month of January and the year 2024 will be matched. The result is 4400, which is the sum of all the sales amounts in January 2024.
You can also use a cell reference instead of typing the date directly in the formula. For example, if you have the date “01/2024” in cell F1, you can use this formula:
=SUMIF (B2:B9, F1, C2:C9)
This formula will return the same result as the previous one, but it will update automatically if you change the value in cell F1.
You can also use logical operators to specify a date range in the criteria argument. For example, to find the total sales for the first quarter of 2024, you can use this formula:
=SUMIF (B2:B9, “>=01/01/2024”, C2:C9) – SUMIF (B2:B9, “>03/31/2024”, C2:C9)
This formula will evaluate the values in the range B2:B9 and sum the corresponding values in the range C2:C9 if they are greater than or equal to “01/01/2024” and subtract the sum of the values that are greater than “03/31/2024”. This means that any date that falls between “01/01/2024” and “03/31/2024” (inclusive) will be matched. The result is 1400, which is the sum of the sales amounts for products A, B, C, and D.
Other Approaches
The SUMIF function is useful for summing values that meet a single condition, but what if you want to sum values that meet multiple conditions? For example, what if you want to find the total sales for the product A in the region North? In this case, you can use the SUMIFS function, which allows you to specify multiple criteria and ranges. The syntax of the SUMIFS function is:
=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
where:
- sum_range is the range of cells that you want to sum. This argument is required.
- criteria_range1 is the first range of cells that you want to evaluate by the first criteria. This argument is required.
- criteria1 is the first condition that must be met for a cell to be included in the sum. This argument is required. It can be a number, text, date, logical expression, cell reference, or another Excel function.
- criteria_range2, criteria2, … are additional ranges and criteria that you can specify to narrow down the sum. These arguments are optional. You can specify up to 127 pairs of criteria ranges and criteria.
For example, to find the total sales for the product A in the region North, you can use the following SUMIFS formula:
=SUMIFS (C2:C9, A2:A9, “A”, B2:B9, “North”)
This formula will evaluate the values in the ranges A2:A9 and B2:B9 and sum the corresponding values in the range C2:C9 if they match the criteria “A” and “North”. The result is 500, which is the sales amount for product A in the region North.
You can also use the SUMPRODUCT function to achieve the same result as the SUMIFS function. The SUMPRODUCT function multiplies the corresponding values in the given arrays and returns the sum of the products. The syntax of the SUMPRODUCT function is:
=SUMPRODUCT (array1, [array2], …)
where:
- array1, array2, … are the arrays of values that you want to multiply and sum. These arguments are required. You can specify up to 255 arrays, but they must have the same size and shape.
For example, to find the total sales for the product A in the region North, you can use the following SUMPRODUCT formula:
=SUMPRODUCT ((A2:A9=“A”)(B2:B9=“North”)(C2:C9))
This formula will evaluate the values in the ranges A2:A9, B2:B9, and C2:C9 and multiply them by the logical expressions (A2:A9=“A”) and (B2:B9=“North”). The logical expressions will return either 1 or 0, depending on whether the condition is met or not. The result is 500, which is the same as the previous example.
