Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
I'm trying to get a count of cells that have a range of data for instance I
want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
One way:
=COUNTIF(rng,"=10")-COUNTIF(rng, "20") Another: =COUNTIF(rng,"<=20") - COUNTIF(rng, "<10") Another: =SUMPRODUCT(--(rng=10),--(rng<=20)) see http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of "--" In article , "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
Try
=SUMPRODUCT((A1:A10=10)*(A1:A6<=20)) For info on sumproduct see http://www.xldynamic.com/source/xld.SUMPRODUCT.html - John www.JohnMichl.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
Try
=SUMPRODUCT((A1:A10=10)*(A1:A10<=20)) For info on sumproduct see http://www.xldynamic.com/source/xld.SUMPRODUCT.html - John www.JohnMichl.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
Disregard first post. The second range should be the same as the first.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
That would be the count of cells =10 minus the count of cells 20. So
=countif(a:a,"=10")-countif(a:a,"20"). (Note that this includes cells equal to 10 and those equal to 20; if you only want one endpoint included, adjust the comparison operators accordingly). HTH. --Bruce "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
still not working correctly. I have 12 cells that have data, of the 12 cells
2 have the data range between 10 - 20 so I'm looking for the formula to give me a count of 2 but the formulas below give me a count of 10 which makes sense since it's subtracting. Please help! "JE McGimpsey" wrote: One way: =COUNTIF(rng,"=10")-COUNTIF(rng, "20") Another: =COUNTIF(rng,"<=20") - COUNTIF(rng, "<10") Another: =SUMPRODUCT(--(rng=10),--(rng<=20)) see http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of "--" In article , "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
but I don't want to minus the cells, I just want a count of how many cells
are between the data range, for example I have 12 cells of which 2 have data between 10 - 20 so the formula should return 2. Please help "bpeltzer" wrote: That would be the count of cells =10 minus the count of cells 20. So =countif(a:a,"=10")-countif(a:a,"20"). (Note that this includes cells equal to 10 and those equal to 20; if you only want one endpoint included, adjust the comparison operators accordingly). HTH. --Bruce "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
But it is subtracting greater than or equal to 20 from greater than or equal
to 10 which is correct, the error must be between the chair and the computer, to show what I mean assume these are the 12 values and they are in A1:A12 1 2 3 4 5 12 19 21 22 23 24 25 now =COUNTIF(A1:A12,"=10") will return 7 (12, 19, 21, 22, 23, 24, 25) =COUNTIF(A1:A12,"=20") will return 5 (21, 22, 23, 24, 25) thus =COUNTIF(A1:A12,"=10)-COUNTIF(A1:A12,"=20") which is the same as =7-5 will return 2 -- Regards, Peo Sjoblom "bsantona" wrote in message ... still not working correctly. I have 12 cells that have data, of the 12 cells 2 have the data range between 10 - 20 so I'm looking for the formula to give me a count of 2 but the formulas below give me a count of 10 which makes sense since it's subtracting. Please help! "JE McGimpsey" wrote: One way: =COUNTIF(rng,"=10")-COUNTIF(rng, "20") Another: =COUNTIF(rng,"<=20") - COUNTIF(rng, "<10") Another: =SUMPRODUCT(--(rng=10),--(rng<=20)) see http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of "--" In article , "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
I think you do want to subtract the second range. Let's consider your
example a little deeper. You have 12 cells, 2 are in the range 10-20. Suppose 4 are in the range 20+. Then there would be 6 in the range 10+. So 6 (in the 10+ range) minus 4 (in the 20+ range) leaves 2 in the target 10-20 range. That's pretty much what the suggested formula does: count the 10+'s then subtract the 20+'s. "bsantona" wrote: but I don't want to minus the cells, I just want a count of how many cells are between the data range, for example I have 12 cells of which 2 have data between 10 - 20 so the formula should return 2. Please help "bpeltzer" wrote: That would be the count of cells =10 minus the count of cells 20. So =countif(a:a,"=10")-countif(a:a,"20"). (Note that this includes cells equal to 10 and those equal to 20; if you only want one endpoint included, adjust the comparison operators accordingly). HTH. --Bruce "bsantona" wrote: I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
If you really only have 2 cells that are between 10 and 20, inclusive,
then all three of the formulae I gave you will return 2. In article , "bsantona" wrote: still not working correctly. I have 12 cells that have data, of the 12 cells 2 have the data range between 10 - 20 so I'm looking for the formula to give me a count of 2 but the formulas below give me a count of 10 which makes sense since it's subtracting. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif functions
Try : =COUNTIF(Range,"10") - COUNTIF(Range,"20") As I understand your
question, you want a count of the number of cells that have a a value *between* (!) 10 and 20 ( that are equal to 11 as a minimum and equal to 19, as a maximum).I think this will do the job for you. "bsantona" wrote in message ... I'm trying to get a count of cells that have a range of data for instance I want a count of cells that have data between 10 - 20. Any ideas would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combined two countif functions | Excel Discussion (Misc queries) | |||
Multiple Functions IF, AND, COUNTIF, MATCH | Excel Worksheet Functions | |||
COUNTIF and Nested Functions? | Excel Worksheet Functions | |||
COUNTIF functions | Excel Worksheet Functions | |||
How do I combine COUNTIF and AND functions in Excel | Excel Worksheet Functions |