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!!!!

hi CMO,

you could use sumproduct as:

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

hope this helps
Regards from Brazil
Marcelo

"CMO" escreveu:

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!!!!

=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")


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=549508

Thanks a lot, works perfectly.

Goodbye from sunny England:-)

"Marcelo" wrote:

hi CMO,

you could use sumproduct as:

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

hope this helps
Regards from Brazil
Marcelo

"CMO" escreveu:

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!!!!

* 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)

"Marcelo" wrote in message
...
hi CMO,

you could use sumproduct as:

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

hope this helps
Regards from Brazil
Marcelo

"CMO" escreveu:

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!!!!

Hi Bob, thanks for the feedback,
regards
Marcelo


"Bob Phillips" escreveu:

* 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)

"Marcelo" wrote in message
...
hi CMO,

you could use sumproduct as:

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

hope this helps
Regards from Brazil
Marcelo

"CMO" escreveu:

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!!!!