Besides the SUMIF function not working and returning incorrect results, one of the common errors users encounter with Excel functions is the summing of values. Users may also encounter other errors while using the function to calculate sums.
The SUMIF function in Excel is used to calculate the sum of values based on specific conditions. While using this Excel function, you may encounter some errors.
Fixing common Sumif function errors in Excel
So what are these errors, and how do you fix them? Please read on for more information below.
Table of Contents:
Common Errors When Using the SUMIF Function in Excel .
1. Syntax Errors: The SUMIF function in Excel doesn't work .
1.1. Incorrect Criteria Definition .
1.2. Errors When Using Comparison Operators in the SUMIF Function in Excel .
1.3. The SUMIF function doesn't work due to incorrect data formatting .
1.4. Using the SUMPRODUCT function if the SUMIF function still doesn't work .
2. VALUE Errors When Using the SUMIF Function in Excel .
3. Error: The string in the criteria is longer than 255 characters .
Common errors when using the SUMIF function in Excel
1. Syntax error: Excel's SUMIF function is not working.
The SUMIF function syntax error in Excel is a common mistake that users often encounter, even experienced users.
Basically, the structure of the SUMIF function is as follows:
=SUMIF(condition_range,condition,sum range)
In there:
- The first parameter , condition_range : This is the selected range containing the condition cells. Your condition will only be checked within this range.
- The second parameter, condition : This is the condition to be checked for the condition_range parameter.
- sum range: This is the range or area to be summed.
Below are details of common Excel SUMIF function syntax errors that users often encounter and how to fix them:
1.1. Incorrect definition of criteria
The criteria for the Excel SUMIF function are defined differently in different situations. To make it easier to understand, let's look at some examples below:
Example 1: Suppose we have the following dataset:
The requirement here is to calculate the total quantity on March 13, using the function syntax shown below:
=SUMIF(A2:A20,1-mar-13,C2:C20)
However, in this case, the Excel SUMIF function syntax will return the value 0. So what is the reason for this?
The data we are working with is formatted as date data. In Excel, date data must be represented as numbers.
However, because Excel's SUMIF function accepts text-formatted data as criteria, even if we use the syntax below, the function will still return the value:
=SUMIF(A2:A20,"1-mar-13",C2:C20)
Or:
=SUMIF(A2:A20,"1-mar-2013",C2:C20)
The numerical equivalent of 1-mar-13 is 41334. Therefore, using the function syntax below will also return the equivalent value:
=SUMIF(A2:A20,41334,C2:C20)
Note: In this case, the criterion is a number, so we don't need to use quotation marks.
An important note is that if the Excel function includes date data, we need to check if the data format is correct. Sometimes, the reason why the SUMIF function doesn't work or return the correct value is due to incorrect data formatting.
1.2. Errors when using comparison operators in the Excel SUMIF function
For example, here the requirement is to calculate the total number of days after March 1, and the standard function syntax should be:
=SUMIF(A2:A20,">1-Mar-13",C2:C20)
But it is not:
=SUMIF(A2:A20,A2:A20>"1-Mar-13",C2:C20)
The symbol > must be enclosed in quotation marks.
Assuming the criterion is located in a cell, such as cell F3, the standard function syntax would be as follows:
=SUMIF(A2:A20,">"&F3,C2:C20)
In this case, we use a comparison operator, and this operator must be enclosed in double quotes.
Note: When calculating the sum of values, if a value matches within the criteria range, we don't need to use the "=" sign; simply write the value or provide a reference to that value as the criterion.
1.3. The SUMIF function is not working due to incorrect data format.
The Excel SUMIF function is used to calculate the sum of numerical values. Therefore, if the function is not working correctly, we first need to check the sum range and ensure the formatting is appropriate.
Sometimes we import data from different sources that may use different data formats, even numerical data may be formatted as text, and this can cause errors in the SUMIF function.
To fix this error, first select a cell containing numerical and text values, then press Ctrl + Space to select the entire column. Next, click the small exclamation mark icon in the upper left corner of the cell. A menu will appear with the " Convert to numbers" option . Click this option to convert all values within the selected range to number format.
If the above solution is not available, another approach is to use the VALUE function to convert the text format string into a numeric format string.
Once completed, paste the values into the SUMIF function formula.
Syntax of the SUMIF function to calculate total time
In some cases, using the SUMIF function to calculate total time may lead to errors.
For example, let's say we use the time format: Hours (HH) : Minutes (MM) : Seconds (SS). The requirement is to calculate the total time on March 1, and the function syntax is as follows:
=SUMIF(A2:A20,F3,C2:C20)
However, the above function will return incorrect results. This is because date and time values are handled differently in Excel.
In Excel, 1 hour is equivalent to 1/24 of a unit. Therefore, 12 hours is equivalent to 0.5.
To calculate the total time, we will need to perform an additional step of converting the G3 cell format to a time format.
Right-click on the cell containing the value you want to format and select "Time Format". The SUMIF function will then return the correct result.
1.4. Use the SUMPRODUCT function if the SUMIF function still doesn't work.
If the SUMIF function still doesn't work, we'll use the SUMPRODUCT function instead.
For example, let's say the requirement is to calculate the sum of values in the range D2:D20 if the date is equal to F3, we would use the SUMPRODUCT function instead, and the formula would look like this:
=SUMPRODUCT(D2:D20,--(A2:A20=F3))
The position and order of variables in the SUMPRODUCT function don't matter; the following formula will return the same result:
=SUMPRODUCT(--(A2:A20=F3),D2:D20)
Or:
=SUMPRODUCT(--(F3=A2:A20),D2:D20)
2. VALUE error when using the SUMIF function in Excel
The VALUE error occurs because the formula contains SUMIF, COUNTIF, or COUNTBLANK functions that reference cells in the closed workbook. To fix this VALUE error , we will use a combination of SUM and IF functions in the array formula.
Array formulas are formulas that can perform multiple calculations on one or more items in an array. Array formulas operate on two or more sets of data called array parameters.
For example, this instance requires calculating the total revenue from seafood products. The result will be added to the Reporting Workbook, and the source data will be in the Data Workbook.
Follow the steps below (this applies to Excel 2010, 2013, and 2016):
Step 1: First, open the workbook containing the source data (Data Workbook).
Step 2: Open the workbook containing the formulas (Report Workbook).
Step 3 : Select cell C5 in the Report Workbook.
Step 4: Click the FX button on the formula bar to find the SUM function.
Step 5 : Next, on the formula bar, in the Name Box menu , find and click IF to nest the IF function with the SUM function.
Step 6: If the IF function is not displayed, click the More Functions option and select the IF function .
Step 7: Enter the arguments 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: Press Ctrl + Shift + Enter to complete the array formula.
Step 9: If a message appears asking you to edit the formula, click Yes to continue.
By using this method, you can avoid encountering value errors when working with a workbook that is not open.
3. String error: The criterion has more than 255 characters.
The SUMIF and SUMIFS functions may return incorrect results if the string is longer than 255 characters.
The solution to fix this error is to shorten the string. The simplest way to shorten a string is to use the CONCATENATE function or the & operator to split the value into multiple strings.
For example:
=SUMIF(B2:B12,"long string"&"other long string")
Above is a summary of some common errors when using the SUMIF function in Excel and how to fix them. Additionally, readers can refer to other articles on TipsMake to learn more about how to fix the Sum function not adding in Excel.