COUNTIF provides weird results
 

Hi,

I had never expected the result that comes out from this example:
Excel cannot mix text and figures in 1 single list of values. Her
comes what happens:

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.

Does anybody have any suggestions?

Thank you

COUNTIF provides weird results
 

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

COUNTIF provides weird results
 

Thank you Harlan,

However, sumproduct does not seem to work differently than countif !!!

I get the same results

COUNTIF provides weird results
 

On Feb 10, 9:50 am, wrote:

Sorry, it works perfectly.

Thank you

COUNTIF provides weird results
 

"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