Environment: Excel 2021 under Windows 11 Pro 64.
We know that Excel's CountIf() has the nasty behavior of converting text that looks like a number to a number before counting. But now, it appears that it doesn't even do those conversions correctly on hex values.
Consider the following spreadsheet:
| Decimal | Hex | Hex 4 | Count decimal | Count hex | Count hex 4 | Note |
|---|---|---|---|---|---|---|
| Manual | =DEC2HEX(A4) | =DEC2HEX(A4, 4) | =COUNTIFS(A:A, A4) | =COUNTIFS(B:B, B4) | =COUNTIFS(C:C, C4) | Manual |
| 0 | 0 | 0000 | 1 | 1 | 3 | |
| 1 | 1 | 0001 | 1 | 2 | 2 | |
| 224 | E0 | 00E0 | 1 | 1 | 3 | Treating 0^0 as 0 |
| 225 | E1 | 00E1 | 1 | 1 | 3 | 0^1 = 0 |
| 481 | 1E1 | 01E1 | 1 | 2 | 2 | 1^0 = 1 |
| 482 | 1E2 | 01E2 | 1 | 2 | 2 | 1^2 = 1 |
| 483 | 1E3 | 01E3 | 1 | 1 | 1 | 1^3 = 1 |
| 7680 | 1E00 | 1E00 | 1 | 2 | 2 | 1^0 = 1 |
| 7681 | 1E01 | 1E01 | 1 | 2 | 2 | 1^1 = 1 |
| 7682 | 1E02 | 1E02 | 1 | 2 | 2 |
We can see that:
1 in column "Count hex" for 0 indicates that nothing else in column "Hex" is being treated as equal to zero, which is correct.1s in column "Count hex" for E0 and E1 indicate that they are not treated as equivalent to numbers, which is correct.3s in column "Count hex 4" indicate that Excel is treating 0000, 00E0, and 00E1 as equal to each other. This is correct for 0000 and 00E1, which are both equal to zero. It's debatable for 00E0, which is ambiguous, but usually treated as equal to 1.2 in each of columns "Count hex" and "Count hex 4" for 1 and 0001 indicate that those values are each treated as equal to one other value in their columns, which is wrong because there are many other values in both columns that are equal to 1.1 in each of columns "Count hex" and "Count hex 4" for 1E3 and 01E3 indicate that those values are each treated as NOT equal to any other value in their columns, which is wrong because they are both equal to 1 and therefore equal to many other values in both columns.2s in the table make no sense because they are all for hex values that are equal to 1. They should all be 7s.Is this another bug in Excel, or is there some reasonable explanation for this behavior?
Everything here is working as expected. The confusion comes over the exponential notation: if x and y are numbers, xEy means "x multiplied by 10 to the power y" - not "x to the power y".
For example, 1E2 means "1 times 10^2" = 1 * 100 = 100, while 0E0 means, unambiguously, "0 multiplied by 10^0", which is 0 (because 10^0 is well defined as being 1).
So Excel's implicit conversions of your hex values to numbers are as follows:
COUNTIFS().)To see the implicit conversions in Excel, try adding two new columns which apply the operation *1 to your Hex and Hex 4 columns. You will see exactly the numerical values to which Excel converts them.
Applying your COUNTIFS() formula on the converted columns will be identical to the results in your table, except that E0 and E1 will have become #VALUE! errors and will therefore be treated as equal by COUNTIFS().