COUNTIF provides weird results

  • Thread starter Thread starter vicente.soler
  • Start date Start date
V

vicente.soler

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
 
(e-mail address removed) 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.
Click to expand...
....

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
 
Thank you Harlan,

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

I get the same results
 
On Feb 10, 9:50 am, (e-mail address removed) wrote:

Sorry, it works perfectly.

Thank you
 
Harlan Grove said:
(e-mail address removed) wrote...

poorly documented, and it'd surprise me if any of the
Click to expand...

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


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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top