Countif on Range
 

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?

Countif on Range
 

Countif(X,"<="&K25)

There's nothing wrong with your formula.

Try this one and see what you get:

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

--
Biff
Microsoft Excel MVP


"gtslabs" wrote in message
...
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?

Countif on Range
 

On Nov 27, 1:32*pm, "T. Valko" wrote:
Countif(X,"<="&K25)

There's nothing wrong with your formula.

Try this one and see what you get:

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

--
Biff
Microsoft Excel MVP

"gtslabs" wrote in message

...



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?- Hide quoted text -

- Show quoted text -

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

Countif on Range
 

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


--
__________________________________
HTH

Bob

"gtslabs" wrote in message
...
On Nov 27, 1:32 pm, "T. Valko" wrote:
Countif(X,"<="&K25)

There's nothing wrong with your formula.

Try this one and see what you get:

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

--
Biff
Microsoft Excel MVP

"gtslabs" wrote in message

...



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?- Hide quoted text -

- Show quoted text -

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

Countif on Range
 

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: EditCopy
Now, select the range "X".
Then: EditPaste SpecialAddOK

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

--
Biff
Microsoft Excel MVP


"gtslabs" wrote in message
...
On Nov 27, 1:32 pm, "T. Valko" wrote:
Countif(X,"<="&K25)

There's nothing wrong with your formula.

Try this one and see what you get:

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

--
Biff
Microsoft Excel MVP

"gtslabs" wrote in message

...



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?- Hide quoted text -

- Show quoted text -

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

Countif on Range
 

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

"gtslabs" wrote in message
...
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?