Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Hey everyone,
Excel 2000 I have a range of cells like so (each number is in it's own cell) 50 20 30 60 90 30 The FIRST cell (in this case, "50") is the 'recommended value'. What I want to do is create a function that looks at all the other 'variable' cells (20, 30, 60....) as one whole range and checks to see if any one of them are less than the recommended value of 50. If so, I want to see a count of how many 'variables' were less than 50. So in this example, I would like to see the answer of "3", because there are three variables in that range of cells that are less than 50. Hope that makes sense. Assuming it would be some kind of IF / COUNTIF, but can't figure out how to do it. Thanks so much! J |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Assuming source data in A1:E1,
In F1: =COUNTIF(A1:E1,"<"&A1) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Access Joe" wrote: Excel 2000 I have a range of cells like so (each number is in it's own cell) 50 20 30 60 90 30 The FIRST cell (in this case, "50") is the 'recommended value'. What I want to do is create a function that looks at all the other 'variable' cells (20, 30, 60....) as one whole range and checks to see if any one of them are less than the recommended value of 50. If so, I want to see a count of how many 'variables' were less than 50. So in this example, I would like to see the answer of "3", because there are three variables in that range of cells that are less than 50. Hope that makes sense. Assuming it would be some kind of IF / COUNTIF, but can't figure out how to do it. Thanks so much! J |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Errata, range should exclude recommended value in A1, viz:
In F1: =COUNTIF(B1:E1,"<"&A1) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Assuming your data is in row 1, with the value 50 in A1, you can put
this in a different row: =COUNTIF(1:1,"<"&A1) If the data is in column A, starting with A1, then put this in a different column: =COUNTIF(A:A,"<"&A1) Hope this helps. Pete On Jul 30, 11:13*pm, Access Joe wrote: Hey everyone, Excel 2000 I have a range of cells like so (each number is in it's own cell) 50 * 20 * 30 * 60 *90 * 30 The FIRST cell (in this case, "50") is the 'recommended value'. *What I want to do is create a function that looks at all the other 'variable' cells (20, 30, 60....) as one whole range and checks to see if any one of them are less than the recommended value of 50. *If so, I want to see a count of how many 'variables' were less than 50. *So in this example, I would like to see the answer of "3", because there are three variables in that range of cells that are less than 50. Hope that makes sense. *Assuming it would be some kind of IF / COUNTIF, but can't figure out how to do it. Thanks so much! J |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Max,
As A1 can never be less than A1 then it will not be included in the count, so you can include it in the range as per your first post. Pete On Jul 31, 12:01*am, Max wrote: Errata, range should exclude recommended value in A1, viz: In F1: =COUNTIF(B1:E1,"<"&A1) -- Max Singaporehttp://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
On Jul 31, 12:01*am, Max wrote:
Errata, range should exclude recommended value in A1, viz: In F1: =COUNTIF(B1:E1,"<"&A1) -- Max Singaporehttp://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- Why isn't it =COUNTIF(B1:F1,"<A1") ??? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
If you include the A1 within the quotes it will be taken as the
literal string "A1" rather than the contents of A1. The OP did have 6 numbers, so the range should include F1. Hope this helps. Pete On Jul 31, 12:07*am, robzrob wrote: Why isn't it =COUNTIF(B1:F1,"<A1") * ??? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
That ROCKS. Thank you guys!
"Max" wrote: Errata, range should exclude recommended value in A1, viz: In F1: =COUNTIF(B1:E1,"<"&A1) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Yes. But I was thinking it's a clearer way to implement the criteria
-- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Pete_UK" wrote in message ... Max, As A1 can never be less than A1 then it will not be included in the count, so you can include it in the range as per your first post. Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range of cells using an IF
Welcome. Do take a moment to press the "Yes" buttons (like the one below) in
all the responses that helped, won't you? -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Access Joe" wrote: That ROCKS. Thank you guys! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to count cells in a range with a given backcolor? | Excel Worksheet Functions | |||
Count Certain Cells in Specified Range | Excel Worksheet Functions | |||
count cells in a range | Excel Worksheet Functions | |||
Count occurances in range of cells | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |