Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Countif for a range of values
I want to check a range of numeric values and count the number that are
between 5 and 10 say. I've tried "=COUNTIF(A1:A30, "5 and <10") but doesn't seem to work. Can this be done ? PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and then sum separately, but looking for a neater way to do it. Thanks iMartyn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Countif for a range of values
If it's a continuous range so as [5,10), you can get there by counting those
five or greater and subtracting the count of those greater than or equal to 10: =countif(a1:a30,"=5")-countif(a1:a30,"=10") "iMartyn" wrote: I want to check a range of numeric values and count the number that are between 5 and 10 say. I've tried "=COUNTIF(A1:A30, "5 and <10") but doesn't seem to work. Can this be done ? PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and then sum separately, but looking for a neater way to do it. Thanks iMartyn |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Countif for a range of values
One way:
=COUNTIF(A1:A30,"<10")-COUNTIF(A1:A30,"<=5") and another: =SUMPRODUCT(--(A1:A305),--(A1:A30<10)) =sumproduct() likes to work with numbers. The -- stuff changes trues to 1's and falses to 0's. iMartyn wrote: I want to check a range of numeric values and count the number that are between 5 and 10 say. I've tried "=COUNTIF(A1:A30, "5 and <10") but doesn't seem to work. Can this be done ? PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and then sum separately, but looking for a neater way to do it. Thanks iMartyn -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Countif for a range of values
Elegant ! Thanks very much.
"bpeltzer" wrote: If it's a continuous range so as [5,10), you can get there by counting those five or greater and subtracting the count of those greater than or equal to 10: =countif(a1:a30,"=5")-countif(a1:a30,"=10") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Countif for a range of values
Wow- That's really A-Level. Thanks.
"Dave Peterson" wrote: One way: =COUNTIF(A1:A30,"<10")-COUNTIF(A1:A30,"<=5") and another: =SUMPRODUCT(--(A1:A305),--(A1:A30<10)) =sumproduct() likes to work with numbers. The -- stuff changes trues to 1's and falses to 0's. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How many values appear more than once in a range? | Excel Worksheet Functions | |||
Summary of values from a date range | Excel Worksheet Functions | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |