Counting data between 2 values

C

CMO

Hi,

I have a list of durations on 1 cell (as below).

1
21
54
72
95
170
etc....

I want to count how many of them are between 0 and 60, how many between 61
and 120, so on and so forth. I have tried using Countif, but can't get it to
work. My last attempt was =count(if(and('Site
list_South'!$E$2:$E$5000>=0,'Site list_South'!$E$2:$E$5000<=60)) and it
failed miserably.

Please Help!!!!
 
M

Marcelo

hi CMO,

you could use sumproduct as:

=sumproduct(--(a1:a100>=1)*--(a1:a100<=60))

hope this helps
Regards from Brazil
Marcelo

"CMO" escreveu:
 
B

Bearacade

=countif(e2:e5000,">=0")-countif(e2:e5000,">60")
=countif(e2:e5000,">=61")-countif(e2:e5000,">120")
=countif(e2:e5000,">=121")-countif(e2:e5000,">180")
=countif(e2:e5000,">=181")-countif(e2:e5000,">240"
 
B

Bob Phillips

* and -- is built-in redundancy, only need one or the other

=sumproduct(--(a1:a100>=1),--(a1:a100<=60))

or

=sumproduct((a1:a100>=1)*(a1:a100<=60))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top