The SUMIF function in Excel calculates the sum of values that meet a certain condition. When combined with other functions like COUNTIF or VLOOKUP, you can optimize data processing. This article will guide you on how to use SUMIF in detail and effectively.
The SUMIF function helps calculate conditional sums in Excel. You can apply it to numerical data, text, or dates to process information quickly.
Quick overview:
1. What is SUMIF?
2. Syntax
3. Example of calculating a sum based on a condition
4. Some notes
1. Define the SUMIF function
SUMIF is a function that calculates the sum of values within a range that meet its specified conditions. Using this function allows you to:
- Quickly calculate the sum of a series of numbers based on your defined conditions.
- Combine it with many other Excel functions.
- Make your work more convenient and efficient.
2. Syntax
Structure: = SUMIF(range; criteria; sum_range).
In there:
- Range: The range of cells to which you want to apply the Criteria.
- Criteria: The criteria that establish the conditions for the value to be summed. It can be a number, an expression, or a text string.
- Sum_range: The range or range of cells to be summed.
Example: Use SumIF to calculate the total allowances for employees in the data table below:
- With the formula for cell D15 being: =SUMIF(C5:C14,"Employee",D5:D14)
=> We get the result as follows: 2,500,000. When we check the result, we see that in the example, this Sumif function has 5 people with the job title "Employee", so the total allowance for those people resulting in 2,500,000 is correct.
* A few notes when using the SUMIF function:
- When sum_range is omitted, the sum will be calculated by Range (replaced by the cells within the evaluation range).
- Case-insensitive. 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 quotation marks ( " " ). For example, ">32", "Taimienphi.vn".
- Cell references in criteria cannot be enclosed in quotation marks, 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 a real question mark or asterisk, type a tilde (~) before the character.
3. Examples
3.1. SUMIF greater than, less than, or equal to
Below are some formulas you can use to compare a value to a given value, indicating whether it is greater than, less than, or equal to it.
3.2. How to use the SUMIF function with text as a condition
- You can add values depending on whether the corresponding cell in another column contains text or not.
Next, let's look at how the non-equivalent Sum function works. In the example below, it's the total amount of all items that are not 'goldfinger bananas':
=SUMIF(A2:A8,"<> goldfinger bananas", C2:C8)
Note: Like other Excel functions, the SUMIF function is not case-sensitive, meaning that whether you write '<>bananas', '<>Bananas', or '<>BANANAS', it will all return the same result.
3.3. Using comparison operators with cell references
- If you want a general formula, you can replace the numerical or text values in the conditions with cell references:
=SUMIF(A2:A8,"<>"&F1, C2:C8)
In this case, you don't need to change the formula to calculate the sum based on different criteria; just enter a new value in the referenced cell.
Note: When using a logical expression with cell references, you must use double quotes ('') to start the text string and ampersand (&) to concatenate and terminate 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. Formulas with wildcards
If your goal is to sum cells based on 'text' conditions and you want to aggregate by partial matching, then you will have to use wildcards.
Here are the available characters:
- The asterisk (*) represents any character
- The question mark (?) represents a single character at a specific location
Example 1: Calculate the total value based on a combination of parts.
Suppose you want to calculate the total cost of all bananas; use the SUMIF formula below:
=SUMIF(A2:A8, '*banana*', C2:C8) - the condition includes the text enclosed in asterisks (*).
=SUMIF(A2:A8, '*'&F1&'*', C2:C8) – the condition includes cell references enclosed in asterisks, using an ampersand (&) before and after a cell reference to concatenate strings.
If you only want to count cells that start or end with a specific text, simply add an asterisk (*) before or after the text:
=SUMIF(A2:A8, 'banana*', C2:C8) - sums the values in C2:C8 if a corresponding cell in column A begins with the word 'banana'.
=SUMIF(A2:A8, '*banana', C2:C8) - sums the values in C2:C8 if a corresponding cell in column A ends with the word 'banana'.
Tip:
Use the concatenation operator (&) to connect wildcards to cell references. In the example above, you could also use the following formula to calculate the total number of 'banana' items in inventory:
=SUMIF(A2:A8, "*"&F1&"*", C2:C8)
Example 2. Calculate the sum of values with specific characters.
If you want to calculate the sum of the values of a number of characters, specifically 6 characters, you can use the formula below:
=SUMIF(A2:A8, "??????", C2:C8)
Example 3. Calculate the sum of the cells corresponding to the text values.
If your spreadsheet contains many different types of data, and you only want to sum the cells that correspond to text values, use the SUMIF formula below:
=SUMIF(A2:A8,'?*', C2:C8) - sums the values in cells C2:C8 if a corresponding cell in column A contains at least one character.
=SUMIF(A2:A8,'*',C2:C8) - includes empty cells containing zero-length strings returned by other formulas, e.g., = ''.
Both of the above formulas ignore non-text values, such as errors, logical operations, numbers, and dates.
Example 4: Using the characters * or ? as normal characters.
If you want to use the * or ? characters like regular characters instead of wildcards, simply add the tilde operator (~) before them.
For example, the SUMIF formula below will add the values in cells C2:C8 if a cell in the corresponding column A, on the same row, contains a question mark (?):
=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 within a range, use a combination of the SUM function with the LARGE or SMALL functions.
Example 1: Adding the maximum/minimum value
If you want to calculate the sum of the largest and smallest values, such as 5, you can directly enter the values in the formula as shown 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 two or more identical values, only the first value will be counted.
Example 2. Calculate the sum of the above/below 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. The INDIRECT function uses the row number representing the value you want to add.
For example, the following formulas calculate the sum of 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 the arrays using the keyboard shortcut Ctrl + Shift + Enter.
Example 3. Calculate the sum of the largest and smallest values of the variables.
If you don't want to change the formula every time you calculate the sum of different cell values, you can enter the value in a cell instead of using the formula. Then, using the ROW and INDIRECT functions, reference a cell containing the variable, in this example, cell E1:
=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums the top values of the variables.
=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1)))) - sums the bottom values of the variables.
It's important to note that these are array formulas, so you'll need to use the Ctrl + Shift + Enter key combination to complete them.
3.6. How to calculate the sum of cells corresponding to empty cells
Blank cells are cells that contain nothing, no formulas, no zero-length strings returned by another Excel function; you use "=" as a condition, as in the SUMIF formula below:
=SUMIF(A2:A10,"=",C2:C10)
If the empty cell contains strings of zero length (e.g., cells with formulas like = ""), use "" as the condition:
=SUMIF(A2:A10,"",C2:C10)
In both formulas above, the cells in column A are evaluated, and if any empty cells are found, the corresponding values are added to column C.
3.7. How to calculate the sum of cells corresponding to non-empty cells
If you want to sum the values of cells in column C when a corresponding cell in column A is empty, use "<>" as the condition in the SUMIF formula:
=SUMIF(A2:A10,"<>",C2:C10)
The formula above counts the values corresponding to all non-empty cells, including zero-length character strings.
3.8. How to use date and time conditions
Essentially, using SUMIF to sum values based on date conditions is similar to using SUMIF with standard conditions like text and numbers.
If you want to calculate the sum of values corresponding to dates greater than, less than, or equal to a specified date, simply use the operators that Free Download introduced above.
Here are a few examples of the Excel SUMIF formula:
If you want to calculate the sum of values based on the current date, you will need to use a combination of the SUMIF and TODAY() functions as shown in the table below:
The screenshot below uses the final formula to calculate the total cost of products shipped this week.
3.9. How to calculate the total value within a specific date range
Many users have asked how to calculate the total value between two days.
The answer is to use a combination, or more accurately, to use two different SUMIF functions. In Excel 2007 or later, you can use SUMIFS for many 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 formula above calculates the sum of the values in cells C2:C9 if the date in column B is between October 1, 2014 and October 31, 2014.
The first SUMIF function calculates the values in cells C2:C9 where the corresponding cell in column B is greater than or equal to the first date (in the example above, October 1st). Then, it subtracts the values that fall after the last date (October 31st) returned by the second function.
3.10. Calculate the sum of the values in the columns.
To understand the issue better, let's consider the following example. Suppose you have a monthly sales revenue summary table. Because there are many different items sold in different stores, you want to calculate the total revenue for each product:
For example, you might want to calculate the total number of apples sold in all stores over the past three 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 this formula will add the corresponding 'apple' values in column C. This is not what you're looking for.
The most logical solution is to create an auxiliary column to calculate the total for each item individually, and then reference that auxiliary column in the sum_range.
Enter the simple SUM formula in cell F2, then enter the formula =SUM(C2:E2) in column F.
The next step is to write the SUMIF formula as follows:
=SUMIF(A2:A9, 'apple', F2:F9)
Or:
=SUMIF(A2:A9, H1, F2:F9)
In the formulas above, range and sum_range have the same number of rows and columns, consisting of 1 column and 8 rows, and return the following result:
If you want to calculate the sum of values without creating extra columns, the simplest way is to write separate SUMIF formulas for each column you want to sum, and then use the SUM function to calculate the sum.
=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))
Alternatively, you can use a more complex array formula (don't forget to use the Ctrl + Shift + Enter key combination):
{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}
Both of the above formulas return the result 2070.
4. Some notes
There are many reasons why SUMIF might not work or return a result. Sometimes the formula you're using doesn't return the value you expect, possibly because of the data type in the cell or parameters that are not suitable for this function. To avoid 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.
The correct formula is: =SUMIF(A1:A3, 'hoa', C1:C3)
Incorrect formula: =SUMIF({1,2,3}, 'hoa', C1:C3)
- How to calculate the sum of values from another sheet or spreadsheet.
Like most other Excel functions, the SUMIF function can reference other sheets or spreadsheets, as long as those sheets and spreadsheets are open.
For example, the formula below calculates the sum of the values in cells F2:F9 in Sheet 1 of Book 1 if a corresponding cell in column A within the same sheet contains the item 'apple':
=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 spreadsheets 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, ensure that the range and sum_range are the same size.
In newer versions of Excel, the range and sum_range parameters do not necessarily have to be the same size. In Excel 2000 and older versions, unequal sizes of range and sum_range can cause errors.
However, in newer versions of Excel such as Excel 2010 and Excel 2016, the SUMIF formula is much more complex, where 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 the VALUE error:
When using the SUMIF function between spreadsheets, if the source spreadsheet is not open, you will receive a VALUE error message. This error occurs if the cells in the spreadsheet containing formulas with SUMIF, COUNTIF, or COUNTBLANK functions are closed. To avoid this error, use a combination of the SUM and IF functions in an array formula.
Array formulas are formulas that can perform multiple calculations on one or more items in an array. Array formulas that operate on two or more values are called array parameters.
The example below calculates the total revenue of various seafood products. The result is returned in a spreadsheet containing the formula and source data located in the data spreadsheet.
Note: You can apply this method to Excel 2010, 2013, and 2016 for Windows.
Step 1: Open the data source workbook.
Step 2: Open the report workbook containing the formula.
Step 3: Select cell C5 in the workbook 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, under Name Box, select IF from the menu .
Step 6: If the IF function is not displayed in the menu, select More Functions , then find and select the IF function.
Step 7: Enter the arguments as shown below:
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 keyboard shortcut Ctrl + Shift + Enter .
Step 9: Select Yes if prompted if the formula is correct. The data range name can be defined for CategoryNames and ProductSales .
The SUMIF function calculates the sum of values in Excel based on a specific condition. If you want to sum with multiple conditions, use the SUMIFS function. In practice, you may encounter syntax errors or #VALUE! errors. To fix this, double-check the function syntax and data formatting.
Besides SUMIF, the IF function in Excel is also widely used but has a different function. IF checks a condition and returns a corresponding result, instead of calculating a sum like SUMIF. When you need to search for data based on a condition, you can combine it with the VLOOKUP function to improve work efficiency.