Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
I'm trying to set up some countif statements that will count the
number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
If your random numbers are in column A then put your upper and lower limits
for each range in columns C and D, e.g. C2 =0, D2 = 9, C3=10, D3=19 and so on then in E2 use this formula copied down =COUNTIF(A:A,"="&C2)-COUNTIF(A:A,""&D2) "Corey" wrote: I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
one way
Set up your class borders in two columns B and C, B1 has 0 C1 has 9 B2 has 10 C2 has 19 b3 has 30 C3 has 29 etc enter =SUMPRODUCT((A$1:A$1000=B1)*(A$1:A$1000<=C1)) in cell D1 and extend down to D10 p.s. the class of 0 to 9 will include any empty cells in the range A1 to A1000 -- Greetings from New Zealand "Corey" wrote in message oups.com... I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
As long as your ranges are always groups of 10 as you posted
Then..with A1:A100 containing your list of numbers Try this: C1: 0 C2: 10 C3: 20 etc E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1)) Copy that formula down Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Corey" wrote: I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
I don't think I'm phrasing this in the most effective way. When you
begin to type a COUNTIF statement, the formula pops up with something as follows: =COUNTIF([Range],[Criteria]) I'm wanting to test for a range as part of my criteria. Something like: =COUNTIF(B59:B83,"19" & "<30") To me, that would imply that for each cell between B59 and B83, add one to the count for any cells that contain some number from 20-29. It doesn't work though in my spreadsheet. I hope that makes my goal more understandable. Thanks for all of your help! On Jan 27, 4:50 pm, Ron Coderre wrote: As long as your ranges are always groups of 10 as you posted Then..with A1:A100 containing your list of numbers Try this: C1: 0 C2: 10 C3: 20 etc E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1)) Copy that formula down Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Corey" wrote: I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
You could also try:
=FREQUENCY([Range],{10,20,30,40,50}) This needs to be array-entered (with ctrl+shift+enter) in the range. On Jan 28, 6:24 am, "Corey" wrote: I don't think I'm phrasing this in the most effective way. When you begin to type a COUNTIF statement, the formula pops up with something as follows: =COUNTIF([Range],[Criteria]) I'm wanting to test for a range as part of my criteria. Something like: =COUNTIF(B59:B83,"19" & "<30") To me, that would imply that for each cell between B59 and B83, add one to the count for any cells that contain some number from 20-29. It doesn't work though in my spreadsheet. I hope that makes my goal more understandable. Thanks for all of your help! On Jan 27, 4:50 pm, Ron Coderre wrote: As long as your ranges are always groups of 10 as you posted Then..with A1:A100 containing your list of numbers Try this: C1: 0 C2: 10 C3: 20 etc E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1)) Copy that formula down Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Corey" wrote: I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function with mulitple criteria?
Your description was fine.
I guess what you need to hear is that COUNTIF won't work exactly the way you want it to. I wish it could use multiple criteria (like what you posted)....but it can't. The responses you got represent some of the ways that Excel CAN deliver the values you're looking for. Does that help? *********** Regards, Ron XL2002, WinXP "Corey" wrote: I don't think I'm phrasing this in the most effective way. When you begin to type a COUNTIF statement, the formula pops up with something as follows: =COUNTIF([Range],[Criteria]) I'm wanting to test for a range as part of my criteria. Something like: =COUNTIF(B59:B83,"19" & "<30") To me, that would imply that for each cell between B59 and B83, add one to the count for any cells that contain some number from 20-29. It doesn't work though in my spreadsheet. I hope that makes my goal more understandable. Thanks for all of your help! On Jan 27, 4:50 pm, Ron Coderre wrote: As long as your ranges are always groups of 10 as you posted Then..with A1:A100 containing your list of numbers Try this: C1: 0 C2: 10 C3: 20 etc E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1)) Copy that formula down Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Corey" wrote: I'm trying to set up some countif statements that will count the number of cells within a given range that contain number within a given range. Ex. One column has a list of random numbers from 0-100. The next column will count the number of numbers that fall into a given range. How many of the cells within the first column contain numbers between 0 and 9? 10 and 19? So on and so forth... Any help on this would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Using ADD function within DCOUNT criteria | Excel Discussion (Misc queries) | |||
Countif Function -Nested | Excel Discussion (Misc queries) |