Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif in a value range? | Excel Discussion (Misc queries) | |||
Countif between a range? | Excel Discussion (Misc queries) | |||
Countif between a range? | Excel Worksheet Functions | |||
Using countif with a name of a range | Excel Worksheet Functions | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions |