Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Good Day,
I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Post some samples and your formula
"bobselff" wrote: Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Try something like this:
Lower boundary = 10 Upper boundary = 25 if the number is greater than x and less than Y Are you sure that's what you want? Most of the time what people really mean is =X and <=Y. So, here's both: 10 and <25 =COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25") =10 and <=25 =COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Here is an example of my data and formula:
10,15,29,45 countif(range,"=15")-countif(same range,"<=29) Thanks, Bob "T. Valko" wrote: Try something like this: Lower boundary = 10 Upper boundary = 25 if the number is greater than x and less than Y Are you sure that's what you want? Most of the time what people really mean is =X and <=Y. So, here's both: 10 and <25 =COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25") =10 and <=25 =COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Good Evening,
My data would be say 10,12,15,35,90 my formula I tried was: countif(range,"=12")-countif(same range,"<=35") I am looking for an answer of 3 meaning I want to count for all the numbers between 12 and 35 inclusive. Thanks, Bob "Teethless mama" wrote: Post some samples and your formula "bobselff" wrote: Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Post the *actual real formula* you tried and tell us what result you got and
what result you expected. If you follow the syntax I suggested in my other reply it should work. If it doesn't then I suspect you may have data problems. The data you have may look like numbers but are in fact TEXT. There may be leading/trailing space characters that you can't see that will cause problems. This is a common problem. -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Here is an example of my data and formula: 10,15,29,45 countif(range,"=15")-countif(same range,"<=29) Thanks, Bob "T. Valko" wrote: Try something like this: Lower boundary = 10 Upper boundary = 25 if the number is greater than x and less than Y Are you sure that's what you want? Most of the time what people really mean is =X and <=Y. So, here's both: 10 and <25 =COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25") =10 and <=25 =COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
Try like this:
=COUNTIF(range,"=12")-COUNTIF(range,"35") or =SUM(COUNTIF(range,{"=12","35"})*{1,-1}) "bobselff" wrote: Good Evening, My data would be say 10,12,15,35,90 my formula I tried was: countif(range,"=12")-countif(same range,"<=35") I am looking for an answer of 3 meaning I want to count for all the numbers between 12 and 35 inclusive. Thanks, Bob "Teethless mama" wrote: Post some samples and your formula "bobselff" wrote: Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
I want to thank all of you for your help, it appears the formula which gave
me the correct answer is: =sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30)) Both of you were a great help, Thank you for your time and effort. Bob "T. Valko" wrote: Post the *actual real formula* you tried and tell us what result you got and what result you expected. If you follow the syntax I suggested in my other reply it should work. If it doesn't then I suspect you may have data problems. The data you have may look like numbers but are in fact TEXT. There may be leading/trailing space characters that you can't see that will cause problems. This is a common problem. -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Here is an example of my data and formula: 10,15,29,45 countif(range,"=15")-countif(same range,"<=29) Thanks, Bob "T. Valko" wrote: Try something like this: Lower boundary = 10 Upper boundary = 25 if the number is greater than x and less than Y Are you sure that's what you want? Most of the time what people really mean is =X and <=Y. So, here's both: 10 and <25 =COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25") =10 and <=25 =COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
I want to thank all of you for your help, it appears the formula which gave
me the correct answer is: =sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30)) Both of you were a great help, Thank you for your time and effort. Bob "Teethless mama" wrote: Try like this: =COUNTIF(range,"=12")-COUNTIF(range,"35") or =SUM(COUNTIF(range,{"=12","35"})*{1,-1}) "bobselff" wrote: Good Evening, My data would be say 10,12,15,35,90 my formula I tried was: countif(range,"=12")-countif(same range,"<=35") I am looking for an answer of 3 meaning I want to count for all the numbers between 12 and 35 inclusive. Thanks, Bob "Teethless mama" wrote: Post some samples and your formula "bobselff" wrote: Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count for numbers one and < another in a row of cells
You're welcome!
FYI: the COUNTIF equivalent would be =COUNTIF($A$2:$A$22,"=0")-COUNTIF($A$2:$A$22,"30") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... I want to thank all of you for your help, it appears the formula which gave me the correct answer is: =sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30)) Both of you were a great help, Thank you for your time and effort. Bob "T. Valko" wrote: Post the *actual real formula* you tried and tell us what result you got and what result you expected. If you follow the syntax I suggested in my other reply it should work. If it doesn't then I suspect you may have data problems. The data you have may look like numbers but are in fact TEXT. There may be leading/trailing space characters that you can't see that will cause problems. This is a common problem. -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Here is an example of my data and formula: 10,15,29,45 countif(range,"=15")-countif(same range,"<=29) Thanks, Bob "T. Valko" wrote: Try something like this: Lower boundary = 10 Upper boundary = 25 if the number is greater than x and less than Y Are you sure that's what you want? Most of the time what people really mean is =X and <=Y. So, here's both: 10 and <25 =COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25") =10 and <=25 =COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25") -- Biff Microsoft Excel MVP "bobselff" wrote in message ... Good Day, I am trying to write a formula which would look down a row of cells and give me a count if the number is greater than x and less than Y. Every one i try (i.e. countif, sumif etc.) either returns an error in the formula or a wrong count. Thank you for your time and enery, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Cells with numbers | Excel Discussion (Misc queries) | |||
Count cells with numbers and ignore cells with errors | Excel Discussion (Misc queries) | |||
Count cells with positive numbers | Excel Discussion (Misc queries) | |||
How do I count cells that contain one of several specific numbers? | Excel Worksheet Functions | |||
count cells with unique numbers | Excel Worksheet Functions |