Countif on Range

[gtslabs]

I have a range of data called "X"
I am trying to count the number of values below a cell value say K25
(value in cell is 11)
I am using Countif(X,"<="&K25) and it returns 0 which is wrong.
If I use Countif(X,"<=11") it works.
What am I doing wrong?

 

[T. Valko]

Countif(X,"<="&K25)

There's nothing wrong with your formula.

Try this one and see what you get:

=SUMPRODUCT(--(X<=K25))

 

[gtslabs]

There's nothing wrong with your formula.

Try this one and see what you get:

=SUMPRODUCT(--(X<=K25))

--
Biff
Microsoft Excel MVP






- Show quoted text -

That returns the correct value of 2.
What could be happening?

 

[Bob Phillips]

That suggests that the cells in X are text values not true numbers.


--
__________________________________
HTH

Bob

There's nothing wrong with your formula.

Try this one and see what you get:

=SUMPRODUCT(--(X<=K25))

--
Biff
Microsoft Excel MVP






- Show quoted text -

That returns the correct value of 2.
What could be happening?

 

[T. Valko]

What could be happening?

Hard to say.

COUNTIF will evaluate *text numbers* and numeric numbers as being equal. So,
if the range is text numbers but K25 is a numeric number (or vice versa)
then COUNTIF should work. However, if both the range and K25 are text
numbers then COUNTIF will return 0. The confusing thing is that you say
SUMPRODUCT works.

Here's what I would do.

Make sure K25 is formatted as General (or Number) and then re-enter the
value in K25.

Make sure "X" is also formatted as General (or Number). You can do this all
at one time instead of cell by cell.

Select an empty somewhere that has never been used or formatted.
Copy that empty cell: Edit>Copy
Now, select the range "X".
Then: Edit>Paste Special>Add>OK

This will usually reset the format of the cells and convert *text numbrs* to
numeric numbers.

--
Biff
Microsoft Excel MVP

There's nothing wrong with your formula.

Try this one and see what you get:

=SUMPRODUCT(--(X<=K25))

--
Biff
Microsoft Excel MVP






- Show quoted text -

That returns the correct value of 2.
What could be happening?

 

[Ashish Mathur]

Hi,

What you can do is, type <11 in a cell (say A12) and then use a formula
=countif(range,A12)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com