Counting data between 2 values

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

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

[Marcelo]

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

[CMO]

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

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

"Marcelo" <[email protected]> wrote in message
news:[email protected]...
> 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!!!!
> >
> >

[Marcelo]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!!!
> > >
> > >
>
>
>