How to use the SUMIF function in Excel to calculate the sum based on conditions

 

Excel will be a great assistant if you know how to exploit functions effectively. One of the most useful functions is SUMIF, which helps calculate the sum of values ​​based on specific conditions. This article of Free Download will guide you in detail on how to use the SUMIF function so that you can easily process data intelligently and save time.

Instructions for using the SUMIF function, syntax and illustrative examples

Quick view:
1. What is SUMIF?
2. Formula for calculating the SUMIF function
3. How to use SUMIF in EXCEL
4. Notes when using the SUMIF sum function.

1. Definition of the SUMIF function

SUMIF is a function that calculates the sum of values ​​in a range that satisfy its conditions. When using this function, you will:
- Quickly calculate the sum of a series of numbers according to the conditions you specify.
- Can be combined with many functions in Excel.
- Convenient and helpful in work.
 

2. Formula for calculating the SUMIF function

Structure: = SUMIF(range; criteria; sum_range).

In there:

- Range: The range of cells you want to apply the Criteria to.
Criteria: The Criteria that defines the conditions for the summed value. It can be a number, an expression, or a text string.

- Sum_range: The area to sum or the range of cells to sum.

Example: Use SumIF to calculate the total allowance for employees in the data table below:

- With the formula for cell D15: =SUMIF(C5:C14,"Employee",D5:D14)
=> We get the following result: 2500000. When we check the result again, we see that in the example, this Sumif function has 5 people with the position "Employee", so the total allowance for those people with the result of 2500000 is correct.

* Some notes when using the SUMIF function:
- When sum_range is omitted, the sum will be calculated according to Range (replaced by the cells in the evaluation range).
- SUMIF is not case-sensitive. You can sum values ​​where the text data can be " Taimienphi.vn " or " TAIMIENPHI.VN ".
- Criteria containing letters or mathematical symbols must be enclosed in double quotes ( " " ). For example ">32", "Taimienphi.vn".
- Cell references in the criteria must not be enclosed in double quotes, i.e. "<" &="">
- The characters ? and * can both be used in Criteria. A question mark matches any single character; an asterisk matches any string of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
 

3. Instructions on how to use the SUMIF function in EXCEL

Below are some formulas of the Excel SUMIF function, how to use the SUMIF conditional sum function, SUMIF function exercises with different conditions and data sets that you can refer to.
 

3.1. SUMIF greater than, less than or equal to

- Below are some SUMIF formulas that you can use to compare values ​​greater than, less than, or equal to a given value.
+ Note: Note that in Excel SUMIF formulas, the comparison operator followed by a number or text must always be enclosed in double quotes ("").

 

3.2. How to use the SUMIF function with the condition being text

- SUMIF allows you to add values ​​depending on whether the corresponding cell in another column contains text or not.
+ Note that you will have to use different SUMIF formulas for exact or partial matching, these formulas are listed in the table below.

Next let's see how the non-equivalent Sum function works. In the example below, the total amount of all items that are not 'goldfinger bananas' is:

=SUMIF(A2:A8,"<> goldfinger bananas", C2:C8)

Note: Like other Excel functions, the SUMIF function is not case sensitive, meaning whether you write '<>bananas', '<>Bananas' or '<>BANANAS' it will eventually return the same result.
 

3.3. Using comparison operators with cell references

- If you want a generic Excel SUMIF formula, you can replace the numeric or text values ​​in the criteria with cell references:

=SUMIF(A2:A8,"<>"&F1, C2:C8)

In this case you don't need to change the formula to sum values ​​based on other criteria, just enter a new value in the referenced cell.

Note: When using a logical expression with a cell reference, you must use double quotes ('') to start the text string and ampersands (&) to concatenate and end the string, for example '<>' & F1.

There is no need to use the 'equals' operator (=) before a cell reference, so both formulas below are correct and give equivalent results:

Formula 1: =SUMIF(A2:A8, "="&F1, C2:C8)
Formula 2: =SUMIF(A2:A8, F1, C2:C8)
 

3.4. SUMIF function formula with wildcard characters

If your purpose is to sum cells based on 'text' conditions and want to sum using partial matches, then you will have to use wildcard characters in the SUMIF formula.

Here are the available characters:

- Asterisk (*) - represents any character
- Question mark (?) - represents a single character at a specific position

Example 1: Calculate total value based on partial combination

Suppose if you want to calculate the total amount of all items which are bananas, use the SUMIF formula below:

=SUMIF(A2:A8, '*banana*', C2:C8) - the criteria includes text enclosed in asterisks (*).
=SUMIF(A2:A8, '*'&F1&'*', C2:C8) – the criteria includes cell references enclosed in asterisks, using ampersands (&) before and after a cell reference to concatenate strings.

If you only want to count cells that start or end with a specific text, just add * before or after the text:

=SUMIF(A2:A8, 'banana*', C2:C8) - sums values ​​in C2:C8 if a corresponding cell in column A starts with the word 'banana'.
=SUMIF(A2:A8, '*banana', C2:C8) - sums values ​​in C2:C8 if a corresponding cell in column A ends with the word 'banana'.

Tips: 

Use the concatenation operator (&) to join wildcard characters with cell references. In the above example, you could also use the following formula to sum the 'bananas' items in stock:

=SUMIF(A2:A8, "*"&F1&"*", C2:C8)

Example 2. Sum values ​​with certain characters

In case you want to calculate the total value of some characters, exactly 6 characters, you use the formula below:

=SUMIF(A2:A8, "??????", C2:C8)

Example 3. Sum cells corresponding to text values

If your spreadsheet contains different types of data, and you only want to sum cells that correspond to text values, use the SUMIF formula below:

=SUMIF(A2:A8,'?*', C2:C8) - adds values ​​in cells C2:C8 if a corresponding cell in column A contains at least 1 character.
=SUMIF(A2:A8,'*',C2:C8) - includes empty cells, containing zero-length strings returned by other formulas, for example: = ''.

Both formulas ignore non-text values, such as errors, logical operators, numbers, and dates.

Example 4: Use the * or ? characters as normal characters

If you want to use the * or ? characters as regular characters rather than wildcards, just add the operator (~) before them.

For example, the SUMIF formula below will add the values ​​in cells C2:C8 if a corresponding cell in column A, located on the same row, contains a ?:

=SUMIF(A2:A8, "~?", C2:C8)

 

3.5. Calculate the sum of the largest or smallest values ​​in a data range

To calculate the largest or smallest sum in a range, use the SUM function in combination with the LARGE or SMALL functions. 

Example 1: Add max value / min value

If you want to sum the largest and smallest values, such as 5, you can enter the values ​​directly as in the formula as below:

=SUM(LARGE(B1:B10,{1,2,3,4,5})) - sum of the 5 largest values
​​=SUM(SMALL(B1:B10,{1,2,3,4,5})) - sum of the 5 smallest values

Note: If there are 2 or more identical values, only the first value is counted. 

Example 2. Calculate the sum of the upper/lower values 

If you want to sum multiple values, instead of listing all of them in the formula, you can nest the ROW and INDIRECT functions within the SUM formula. In the INDIRECT function, use the row number that represents the value you want to add.

For example, the corresponding formulas below sum values ​​above and below 15:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:15"))))
=SUM(SMALL(B1:B50,ROW(INDIRECT("1:15"))))

Since these are array formulas, remember to access arrays using the Ctrl + Shift + Enter key combination.

Example 3. Calculate the sum of the largest / smallest values ​​of variables

If you don't want to have to change the formula every time you want to sum different cells, you can enter the value in a cell instead of entering it in the formula. Then use the ROW and INDIRECT functions, referencing a cell containing the variable, in this example cell E1:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums the variables of the top values.
=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums the variables of the bottom values.

One thing to note is that these are array formulas so you will have to use the key combination Ctrl + Shift + Enter to complete.
 

3.6. How to calculate the sum of cells corresponding to empty cells

Blank cells are cells that contain nothing, no formula, no zero-length string, returned by another Excel function, you use "=" as the condition, like in the SUMIF formula below:

=SUMIF(A2:A10,"=",C2:C10)

If the empty cells contain zero-length strings (for example, cells with formulas like =""), use "" as the condition:

=SUMIF(A2:A10,"",C2:C10)

In both the above formulas evaluate the cells in column A and if any blank cells are found, the corresponding values ​​are added to column C.

 

3.7. How to calculate the sum of cells corresponding to non-blank cells

If you want to sum the values ​​of cells in column C when a corresponding cell in column A is blank, use "<>" as the condition in the SUMIF formula:

=SUMIF(A2:A10,"<>",C2:C10)

The above formula counts values ​​corresponding to all non-empty cells, including zero-length character strings.
 

3.8. How to use the SUMIF function with date conditions

Using SUMIF to sum values ​​based on date criteria is basically the same as using SUMIF with standard criteria like text and numbers.

If you want to sum the values ​​corresponding to dates greater than, less than, or equal to the date you specify, just use the operators that Free Download introduced above. 

Here are some examples of Excel SUMIF formulas:

In case you want to sum values ​​based on the current date, you will have to use the SUMIF function in combination with the TODAY() function as shown in the table below:

The screenshot below uses the final formula to calculate the total amount of products shipped during the week.

 

3.9. How to calculate the total value within a specific date range

Many users ask how to calculate the total value between 2 dates?

The answer is to use a combination or more precisely, use 2 different SUMIF functions. In Excel 2007 or higher, you can use SUMIFS for multiple conditions. However, in this article, Free Download will only introduce you to SUMIF.

Here is an example of the SUMIF formula:

=SUMIF(B2:B9, ">=1/10/2014", C2:C9) - SUMIF(B2:B9, ">=1/11/2014", C2:C9)

The above formula sums the values ​​in cells C2:C9 if the dates in column B are between 10/1/2014 – 10/31/2014.

The first SUMIF function calculates the values ​​of cells C2:C9 where the corresponding cell in column B is greater than or equal to the first date (in the above example, October 1). It then subtracts the values ​​that fall after the last date (October 31) returned by the second function.
 

3.10. Calculate the sum of values ​​in columns

To understand the problem, consider the following example. Suppose you have a monthly sales summary. Since there are many different products sold in different stores, you want to calculate the total sales for each product:

For example, you want to calculate the total number of apples sold in all stores over the past 3 months.

As you know, the size of sum_range is determined by the size of the range parameter. That's why you can't use a formula like =SUMIF(A2:A9,'apples',C2:E9), because it will add the corresponding 'apples' values ​​in column C. This is not what you're looking for.

The most logical solution is to create a helper column to sum each item individually, then reference that helper column in sum_range.

Enter a simple SUM formula in cell F2, then enter in column F the formula: =SUM(C2:E2).

The next step now is to write the SUMIF formula as below:

=SUMIF(A2:A9, 'apples', F2:F9)

Or:

=SUMIF(A2:A9, H1, F2:F9)

In the above formulas, range and sum_range have the same number of rows and columns, consisting of 1 column and 8 rows, and return the result:

If you want to sum values ​​without creating a helper column, the simple way is to write separate SUMIF formulas for the columns you want to sum, and then use the SUM function to sum:

=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))

Or alternatively use a more complex array formula (don't forget to use Ctrl + Shift + Enter):

{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}

Both of the above formulas return 2070.
 

 

4. Some notes when using the SUMIF function

There are many reasons why SUMIF does not work and does not return results. Sometimes the formula you use does not return the value you expect, it may be because the data type in the cell or the parameter is not suitable for this function. If you do not want to have errors, you need to check important information such as:

- The range and sum_range parameters must be data ranges, not arrays.

The first (range) and third (sum_range) parameters in a SUMIF formula must be range references, such as A1:A10. If they are arrays, such as {1,2,3}, Excel will return an error message.

Exact formula: =SUMIF(A1:A3, 'flowers', C1:C3)

Incorrect formula: =SUMIF({1,2,3}, 'flower', C1:C3)

- How to sum values ​​from another sheet or spreadsheet.

Like most other Excel functions, the SUMIF function can reference other sheets or spreadsheets, as long as the sheets and spreadsheets are open.

For example, the formula below sums the values ​​in cells F2:F9 in Sheet 1 of Book 1 if a corresponding cell in column A in the same sheet contains the item 'apples':

=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,'apple',[Book1.xlsx]Sheet1!$F$2:$F$9)

However, the above formula will not work if Book 1 is closed. This happens because the data range referenced by the SUMIF formula in the worksheets is not referenced to arrays, and since no arrays are referenced in the range and sum_range parameters, Excel will return the #VALUE! error message.

- To avoid errors, make sure range and sum_range are of the same size.

In newer versions of Excel, the range and sum_range parameters do not have to be the same size. In Excel 2000 and earlier, if the range and sum_range are not the same size, an error will occur. 

However, in newer versions of Excel such as Excel 2010 and Excel 2016, the SUMIF formula is much more complex, in which the sum_range parameter has fewer rows and columns than the range parameter. That is why the range and sum_range parameters must be the same size, to avoid errors.

How to avoid VALUE error:

When using the SUMIF function between worksheets, if the source worksheet is not open, you will receive a VALUE error message. This error occurs if the cells in the worksheet containing the formula with the SUMIF, COUNTIF, or COUNTBLANK functions are closed. Avoid this error by using a combination of the SUM and IF functions in an array formula.

An array formula is a formula that can perform multiple calculations on one or more items in an array. Array formulas operate on two or more values ​​called array parameters.

In the example below, the total revenue of seafood items is calculated. The result is returned in a spreadsheet containing the formula and the source data is in the data spreadsheet.

Note: You can apply this method for Excel 2010, 2013, 2016 for Windows.

Step 1: Open the spreadsheet containing the source data (Data workbook).
Step 2: Open the spreadsheet containing the formula (Report workbook).
Step 3: Select cell C5 in the spreadsheet containing the formula.
Step 4: Use the FX button on the Formula Bar to locate the Sum function.
Step 5: To nest the IF function, on the Formula Bar, in the Name Box section, from the menu, select IF .
Step 6: If the IF function is not displayed in the menu, select More Functions and then find and select the IF function.
Step 7: Enter the arguments as follows:

Logical_test : Data.xlsx!$A$23:$A$30='Seafood'.
Value_If _true: SUM(Data.xlsx!$D$23:$D$30).
Value_if_false: 0.

Step 8: To complete the array formula, use the key combination Ctrl + Shift + Enter .
Step 9: Select Yes if asked if the formula is correct. The name range can be defined for CategoryNames and ProductSales .

Above Free Download uses SUMIF with 1 condition to calculate allowances for employees, to use the sum function with multiple conditions, you switch to using SUMIFS. During the calculation process, you may encounter some errors such as syntax errors, VALUE errors,. To fix this problem, please read   our SUMIF function error article to know how to fix it.

A conditional function similar to SUMIF is IF, which is also commonly used by Excel users. However, the IF function only queries conditions and gives results that match the conditions, not calculating the total like the SUMIF function. You should pay attention to apply it correctly.

Through the example exercises above, you must have understood how to use the SUMIF function on Excel spreadsheets. Hopefully, this article will help you effectively apply  basic functions in Excel  to your work.

In addition to SUMIF, to use the basic Vlookup function - the search function in Excel - well, please refer to our instructions on how to use the Vlookup function .