Explanation
In this example, the goal is to count numbers that contain leading zeros.
The challenge is that Excel can be finicky with leading zeros.
Technically, the values in B5:B16 aretext, as is the value in E5.
However, sometimes text values that contain numbers are converted tonumericvalues as they go through Excel’s calculation engine.
When this happens, the leading zeros will be silently removed, which can cause an incorrect result.
The article below explains the problem in more detail.
For convenience,code(B5:B16) andqty(C5:C16) arenamed ranges.
are configured to use numbers with leading zeros.
We expect a result of 1, but COUNTIF returns 5.
Somewhere in the calculation process, the leading zeros get dropped and all cells evaluate to 1.
This is clearly not the result we want, and shows a limitation of the COUNTIF function.
Note: COUNTIF is in agroup of 8 functionsthat share some particular quirks and limitations.
The nice thing about this approach is that it can be easily extended, as explained below.
The final result from SUMPRODUCT is 7.
SUMPRODUCT is a workhorse function that can solve many tricky problems in Excel.
However, the double negative does no harm and makes the counting and summing formulas easier to compare.
Related functions
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
Criteria can be applied to dates, numbers, and text.
