Most people are unaware that the SUMIFS function has a serious limitation when it comes to codes with leading zeroes. This post shows you how to perform calculations involving codes with leading zeroes. This issue also affects SUMIF, COUNTIF and COUNTIFS.
The image below illustrates the problem.
SUMIFS was added in Excel 2007. A link to the example file is at the bottom of this post.
Formulas from column E are shown in column F. (I used the FORMULATEXT function – added in Excel 2013 – it is great for training and documentation purposes – I wrote a post about it here).
The formula for cell E2 is
=SUMIFS($B$2:$B$7,$A$2:$A$7,D2)
Not sure why, but SUMIFS doesn’t work.
It seems to convert the leading zero codes in numeric codes into numbers before it does the calculation. Leading zeros in alphanumeric codes are handled correctly.
The solution involves my favourite function, SUMPRODUCT. See image below.
The formula in cell E8 is
=SUMPRODUCT(($A$2:$A$7=D8)*($B$2:$B$7))
I find SUMPRODUCT formulas easier to read than SUMIFS formulas because it uses the = sign to specify what equals what.
You need the brackets around the condition ($A$2:$A$7=D8) but you can remove the brackets around the sum range B$2:$B$7. The following formula will also work
=SUMPRODUCT(($A$2:$A$7=D8)*$B$2:$B$7)
I tend to put the brackets around all the ranges to be consistent and to stop having to think which ones need the brackets.
SUMPRODUCT has two limitations
- it is slower to calculate than SUMIFS
- you can’t have any text values in the sum range – $B$2:$B$7 in the above formula – even a single text value in the sum range will result in an error
SUMPRODUCT also has a super power. It can perform calculations on closed files! SUMIFS won’t work at all on closed files.
As I mentioned SUMPRODUCT is my favorite function. It is Excel’s most versatile function. It can also perform counts.
To convert the above sum calculation into a count calculation you use.
=SUMPRODUCT(($A$2:$A$7=D8)*1)
SUMPRODUCT can also use other functions within its brackets to perform calculations that normally require an array formula.
How SUMPRODUCT works will be covered in a separate blog post.
You can download the file I used at the link below.
Please note: I reserve the right to delete comments that are offensive or off-topic.