Excel COUNTIF returns ambiguous results

Question

I'm trying to use Excel's COUNTIF formula on a large range of values to basically create a unique and sorted list of those values. However, several values don't seem to be considered in this operation, so I went to debugging it.

Could you explain to me why the values in column B of the snipet table below are all '0'? What is the catch to obtain a value of '1' when comparing values like the ones below?

I understand I'm probably missing some silly detail...

Thanks in advance

   -----------------
   |   A   |   B   |
   -----------------
1  | 10 B  |   0   |  <-- B1 formula is =COUNTIF(A1;"<52")
   -----------------
2  |  52   |   0   |  <-- B2 formula is =COUNTIF(A2;"<10 B")
   -----------------
3  |  52   |   0   |  <-- B3 formula is =COUNTIF(A3;"=10 B")
   -----------------

EDIT: Let me put this into perspective. Consider the table below.

What I'm trying to achieve exactly is, on column B, construct a list of unique values from column A. I'm doing this by using the following formula on B2, with CTRL+SHIFT+ENTER, and dragging it through cells in B column, B3, B4, etc:

=INDEX($A$2:$A$100;MATCH(0;COUNTIF($A$2:$A$100;"<"&$A$2:$A$100)-SUM(COUNTIF($A$2:$A$100;B$1:B1));0))

However, several values from column A are "skipped", hence why I've tried debuging the COUNTIF formula like I explained above, but still no idea why certain values are skipped, perhaps an error with the formula?

    -----------------------
    |     A    |     B    |
    -----------------------
 1  |          |          |
    -----------------------
 2  |    52    |    52    |
    -----------------------
 3  |   16277  |  16277   |
    -----------------------
 4  |   16277  |  21785   |
    -----------------------
 5  |   16277  | 5411828  |
    -----------------------
 6  |   21785  | 54112528 |
    -----------------------
 7  |  5411828 |   37-50  |
    -----------------------
 8  | 54112528 |   42-B   |
    -----------------------
 9  |   10 B   |  ABC-01  |
    -----------------------
10  |   10-M   |          |
    -----------------------
11  |   15-94  |          |
    -----------------------
12  |  15-971/ |          |
    -----------------------
13  |   16.    |          |
    -----------------------
14  |   16..   |          |
    -----------------------
15  |   22-23  |          |
    -----------------------
16  |   37-50  |          |
    -----------------------
17  |   42-B   |          |
    -----------------------
18  |  ABC-01  |          |
    -----------------------

Even if you get past all the problems with comparing true numbers against text-that-look-like-numbers, none of those countif formulas would return anything but zero. — user4039065

Joseph Mason Joseph Mason · Accepted Answer · 2017-11-16T20:07:09

For the first row, "10 B" is not a numerical value, so it cannot fit the COUNTIF criteria of being less than 52. If that field will always have a number and letter then you could parse out the number with LEFT(A1,2) to get the two digits on the left, and then see if they're less than 52.

For the second and third rows your COUNTIF is looking for criteria that is less than a non-numerical value, so the function won't work. You could COUNTIF if the cell value was equal to that criteria, looking for a match. Or you could change the formula to COUNTIF(A3,"<10") which would then return a count of any cells that are less than 10.

Your primary issue seems to be differentiating between numbers, and numbers with letters. In excel, when you have a cell with letters and/or spaces in addition to numbers, it will typically format to a text field and so mathematical formulas will not use it with out some tweaking.

Excel COUNTIF returns ambiguous results

2 Answers