COUNTIF & COUNTIFS errors for Numbers Stored as Text, Manual Range Data

[Phil_Starke]

Howdy All,

I discovered some issues with COUNTIF & COUNTIFS using Microsoft Excel 365 installed locally on Windows 10 Home.

1) They treat all numbers as numbers, regardless of whether they are stored as text or as numbers. This happens both within the Criteria and the Range. =COUNTIF({"123";123;"0123"},"000123") will return 3 instead of 0. Actually, =COUNTIF(Range,"000123") will return 3, if your range contains {"123";123;"0123"}. This brings me to my 2nd discovery...

2) They cannot accept manually-defined data for a range. =COUNT({"123";123;"0123"}) returns 1. =COUNTA({"123";123;"0123"}) returns 3. =COUNTIF({"123";123;"0123"},"000123") returns a pop-up stating, "There's a problem with this formula." Using F9 to convert a range into data is useful for troubleshooting formulas, but these two won't play nice.

COUNTIF Errors.xlsx 

This is a link to the file on my OneDrive. I was working on a database interface for construction parts that use different formats for part numbers from different vendors such as 123, "0123", & "000123". The COUNTIF is being used as a simple way to confirm the part was on the list (=COUNTIF(PartList,PartNum)>0) for conditional formatting without having to worry about error catching with things like FIND, MATCH, or any of the lengthy LOOKUP families. I'm hoping this is confirmed as a bug, and not as a feature.

I'm posting this to help the community be aware of these limitations in their current form.

[#2]

@Phil_Starke This has always been the behavior of functions like COUNTIF, SUMIF, AVERAGEIF, etc. (not a bug). These functions are not "type" specific when handling numbers and dates. Incidentally, =COUNTIF(Range, "May 2, 1900") will also return 3 in your example, because "May 2, 1900" is recognized as a valid date, which Excel reads as the number 123 (day 123, starting from January 1, 1900).

Furthermore, all "range" arguments must use actual range references, or a function that returns a range reference (ie: INDEX, XLOOKUP, TAKE, DROP). Arrays can only be used in the "criteria" arguments. Attempting to use a static array (or a formula that returns an array object) as the "range" argument will result in the generic "There's a problem with this formula." error message.

The generally accepted workaround for COUNTIF when dealing with numbers formatted as text is to use the SUMPRODUCT function. For example:

=SUMPRODUCT(--(Range=Criteria))

Range=Criteria returns an array of TRUE and FALSE values, the double-negative converts them to 1's and 0's, and the sum is returned, representing the total count.

However, since your actual use case is to confirm that a part number exists within a list, using =COUNTIF(PartList, PartNum)>0 within Conditional Formatting, an alternative method would be to use ISNUMBER / XMATCH as follows:

=ISNUMBER(XMATCH(PartNum, PartList))

XMATCH returns the row number where the first match is found. If no match is found, it returns the #N/A error. ISNUMBER converts the result to TRUE if found or FALSE if not. Unlike COUNTIF, XMATCH is "type" specific, so "0123" will not match with 123.

I hope that helps. Cheers!