Similar to other features used in Excel, SUMIF provides next-level help according to the condition. Well, sometimes you may have to face several issues while using certain features. You may find that the SUMIF is not working properly or it may provide imprecise results. Most beginners find it difficult to use because they don’t have enough practice of using this function. However, never think that experts don’t fail as Excel always enjoys surprising its users.
Behind SUMIF error, there can be many reasons. Here, we will try to discuss every potential reason that leads to SUMIF, not working conditions, and how you can get rid of it. Before moving ahead, let’s check the following points in your formula. These points may help you work the SUMIF formula.
- You may need to evaluate the formula in case if your SUMIF function is returning the #N/A error or any other error. It is 80% guaranteed that your formula will get in line after this action. For this formula evaluation, you need to select the formula cell and open the Formula tab given in the ribbon. Now, find Evaluate Formula option, which is in the Auditing section.
- Writing the correct formula while updating the sheet, the SUMIF function will not return the updated value. Possibly, you can set formula calculation to manual and press the F9 key to recalculate the sheet.
- The format of the values used in the calculation must be checked. Most likely there can be unexpected formats in case if you are importing data from other sources.
If the above-mentioned tricks didn’t help you, try using the options given below. All-time useful tricks for SUMIF not working function.
Syntax Error – SUMIF Not Working
For beginners, syntactical error is the most common thing. As a matter of fact, even experts while using the SUMIF function can make mistakes. Let’s have a look at the syntax of the SUMIF function.
Common Excel SUMIF Formula:
=SUMIF(condition_range,condition,sum range)
Here, the first argument is a range that has your condition. You can check the condition only in this range. The second argument is in the condition itself and that’s what you need to check the condition_range.
Sum_range: It is in the range that you may need some.
It was a brief overview of the syntax of the SUMIF function. Now, let’s move on to clear the syntactical mistakes people often make while using the SUMIF function.
Significant Criteria Mistakes
Most mistakes are made while defining criteria because of the data variation. Likewise, in SUMIF the criteria are defined differently in multiple conditions. For instance:
Suppose we need the sum quantity of date 1-March-13.
We will write this formula as:
=SUMIF(A2:A20,1-mar-13,C2:C20)
Do you think this formula will work or not?
That’s where you will experience the SUM IF not working because it will return 0. Why is it so? You must be wondering why is my SUMIF returning 0.
Do you know that in Excel, the dates are processed as numbers? And we are working with the data, which is formatted as a date. On the other hand, the number can be written except for quotes as criteria. Still, Excel does not return the correct answer. Basically, in Excel, the SUMIF accepts dates as text in criteria, if they are not formatted as a serial number.
Writing this formula will return the right answer:
=SUMIF(A2:A20,”1-mar-13”,C2:C20)
Or
=SUMIF(A2:A20,”1-mar-2013”,C2:C20)
41334 is the equivalent number to 1-mar-13, so writing this formula will return the right answer:
=SUMIF(A2:A20,41334,C2:C20)
Did you notice that there are no quotes used here?
Quotes are not required while giving number criteria. Remember that you have to be alert while working with dates. You can make a mess with dates easily. Having a formula with dates needs you to check it often whether it is formatted correctly or not.
Mistakes While Using Comparison Operator in SUMIF Function
In this case, we will sum up the number of dates later than 1-Mar-13. The right syntax for this will be:
=SUMIF(A2:A20,”>1-Mar-13”,C2:C20)
Not this:
=SUMIF(A2:A20,A2:A20>”1-Mar-13”,C2:C20)
In criteria, we don’t need to add the criteria range as you already told the SUMIF the range in which to look. Now, if the criteria are used in a cell, suppose it is in F3, then how would you use the SUMIF function. Check the following formula:
=SUMIF(A2:A20,”>F3”,C2:C20)
It will give you SUMIF not working error.
Move on to the next syntax:
=SUMIF(A2:A20,>F3,C2:C20)
Again, the SUMIF not working error
Let’s have another syntax example:
=SUMIF)A2:A20,>”F3”,C2:C20)
Not at all.
While using the comparison operator, we have to put ampersand between the operator and the range. The operator should be closed with quotes, and then the SUMIF formula will work properly.
Things to Remember
While using the sum values, if a value matches precisely in the criteria range, equal to sign “=” is not needed. At the place of criteria, you will just write the value or give the reference as you see in the first example.
Have you noticed that new users often use the below syntax when they need an exact match?
=SUMIF(A2:A20,A2:A20=F3,C2:C20)
Well, this is not the right syntax and the SUMIF not working error will again appear. Just the reference is needed while using reference as criteria for exact matches. Here is another example in which you can see the sum of all values given as dates in cell F3:
=SUMIF(A2:A20,F3,C2:C20)
Well, some of the syntactical mistakes mentioned –above that people often make while using the SUMIF function and get the SUMIF not working error. Now, let’s cover some other kinds of reasons for SUMIFs value error.
SUMIF Not Working Because of Uneven Data Format
As you know the SUMIF function deals with numbers that can be summed up. At first, you have to check the sum range whether it is in the proper number format or not. While importing data from other sources, facing uneven data formats is not so rare.
Having numbers formatted as text is possibly well defined otherwise the numbers are not so easy to handle. Here you will see that the sum range has text values rather than numbers. As you know text values can never be summed up, so the result we get will be 0. The green part of the cell shows that the numbers are formatted as text.
Your range possibly includes mixed formats and most likely a few numbers will be formatted as text whereas the rest will be numbers. That way those text-formatted numbers will not be summed up and you will find an exact result.
Use of SUMPRODUCT If SUMIF Not Working Anyway
For whatever reason, if the SUMIF function not working properly, you can use the alternative formula SUMPRODUCT.
For instance, if you need the same result as above, you can still use the SUMPRODUCT function for this.
You have to sum range D2:D20 if the date is equal to F3. Write this formula as:
=SUMPRODUCT(D2:D20,–(A2:A20=F3))
It does not matter what order you have given to the variables. The formula given below will work ideally:
=SUMORODUCT(–A2:A20=F3),D2:D20)
Or this one:
=SUMPRODUCT(–(F3=A2:A20),D2:D20)
To Sum Up
The SUMIF formula not working is not a rare thing as people often make mistakes while using Excel. There are lots of functions and if one has not had enough practice of any function, it can produce errors. Practice aloud and save yourself from errors.
