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

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!