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 |
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 |
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 |
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") |
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. |
All times are GMT +1. The time now is 11:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com