"Harlan Grove" wrote in message
oups.com...
wrote...

poorly documented, and it'd surprise me if any of the


OK I give in - any of the what?
--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Harlan Grove" wrote in message
oups.com...
wrote...
...
My model contains, in A1 to A6 the values

1
2
3
a
b
c

In B1 I input COUNTIF($A$1:$A$6,"<="&$A$1:$A$6) and I copy it down
to B6. I get:

1
2
3
1
2
3

instead of

1
2
3
4
5
6

My question: why if 1 and 2 and 3 are smaller than "a", the count of
values smaller than or equal to "a" returns 1 and not 4. I cannot
understand it.
...

Microsoft built extra intelligence into COUNTIF and SUMIF. When it
comes to matching error values, it can be handy. Unfortunately, it
follows its own logic when it comes to comparing text to numbers. If
you want the result above, don't use COUNTIF. Use separate formulas

=SUMPRODUCT(--($A$1:$A$6<=A1))
=SUMPRODUCT(--($A$1:$A$6<=A2))
=SUMPRODUCT(--($A$1:$A$6<=A3))
=SUMPRODUCT(--($A$1:$A$6<=A4))
=SUMPRODUCT(--($A$1:$A$6<=A5))
=SUMPRODUCT(--($A$1:$A$6<=A6))

Is there a good reason Excel handles <= comparisons differently in
COUNTIF/SUMIF criteria arguments vs elsewhere? No. COUNTIF/SUMIF are
poorly documented, and it'd surprise me if any of the