Hmm, interesting. It's funny how the native function (CountIf) will reduce
everything to numerical constants, whereas a SumProduct will test each
occurance as textual with no conversion. Boy that could get complex! LOL!
Thanks for that Harlan. Appreciate it. :)
--
Regards,
Zack Barresse, aka firefytr
"Harlan Grove" wrote in message
oups.com...
zackb wrote...
Because they are the same - to Excel. You'd need to change your
values to
text to count such instances. This is because the only reason you see
the 0
on the end is in lieu of the cell formatting, which is basically a
mask.
...
You've got part of the truth, but the whole truth is UGLY. I enter the
following *EXACTLY* into A1:A3.
'4.1
'4.1
'4.10
So the single quotes denote ad hoc text entry. Then in another cell I
enter the formula
=COUNTIF(A1:A3,A1)
What does Excel return? 3! COUNTIF is FUBAR! When its second argument
is numeric, so either an actual number or a valid text representation
of a number, COUNTIF treats all cells in its first argument as numeric.
No way to change these semantics.
|