Prev: Days in Text to Dates SPECIAL
Next: Vlookup & relative cell reference?
From: Meebers on 13 Apr 2010 17:02
Got a big long column D containing times formatted as i.e. 13:30. I am
trying to do a countif function for finding the count of inputs between 6 am
and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of
using logic => and < within the countif formula... appreciate any help
here..

From: Mike H on 13 Apr 2010 17:59
Hi,

maybe this for 06:00 to 06:59:59

=SUMPRODUCT((D1:D8>=TIME(6,0,0))*(D1:D8<TIME(7,0,0)))

or you can do this for 06:00 to 06:59:59 and drag down for subsequent hours

=SUMPRODUCT((D1:D8>=TIME(ROW(A6),0,0))*(D1:D8<TIME(ROW(A7),0,0)))
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Meebers" wrote:

> Got a big long column D containing times formatted as i.e. 13:30. I am
> trying to do a countif function for finding the count of inputs between 6 am
> and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of
> using logic => and < within the countif formula... appreciate any help
> here..
>
> .
>
From: T. Valko on 13 Apr 2010 19:28
>Can't find the right combination of using logic
>=> and < within the countif formula...

Like this...

=COUNTIF(A1:A20,">="&TIME(6,0,0))-COUNTIF(A1:A20,">="&TIME(7,0,0))

Might be easier to use this...

=SUMPRODUCT(--(HOUR(A1:A20)=6))

--
Biff
Microsoft Excel MVP


"Meebers" <Noway(a)noway.com> wrote in message
news:4bc4dbe0$0$4978$9a6e19ea(a)unlimited.newshosting.com...
> Got a big long column D containing times formatted as i.e. 13:30. I am
> trying to do a countif function for finding the count of inputs between 6
> am and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of
> using logic => and < within the countif formula... appreciate any help
> here..


From: Meebers on 13 Apr 2010 20:07
Good One!!

"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:OJRJME22KHA.5420(a)TK2MSFTNGP05.phx.gbl...
>>Can't find the right combination of using logic
>>=> and < within the countif formula...
>
> Like this...
>
> =COUNTIF(A1:A20,">="&TIME(6,0,0))-COUNTIF(A1:A20,">="&TIME(7,0,0))
>
> Might be easier to use this...
>
> =SUMPRODUCT(--(HOUR(A1:A20)=6))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Meebers" <Noway(a)noway.com> wrote in message
> news:4bc4dbe0$0$4978$9a6e19ea(a)unlimited.newshosting.com...
>> Got a big long column D containing times formatted as i.e. 13:30. I am
>> trying to do a countif function for finding the count of inputs between 6
>> am and 6:59 am, and 7am and 7:59 etc. Can't find the right combination
>> of using logic => and < within the countif formula... appreciate any
>> help here..
>
>
From: Meebers on 13 Apr 2010 20:21
Thanks Mike....I used the second one. MikeG

"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:E1304D7D-6F9D-4DB5-84C2-D219E3DD2D5E(a)microsoft.com...
> Hi,
>
> maybe this for 06:00 to 06:59:59
>
> =SUMPRODUCT((D1:D8>=TIME(6,0,0))*(D1:D8<TIME(7,0,0)))
>
> or you can do this for 06:00 to 06:59:59 and drag down for subsequent
> hours
>
> =SUMPRODUCT((D1:D8>=TIME(ROW(A6),0,0))*(D1:D8<TIME(ROW(A7),0,0)))
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Meebers" wrote:
>
>> Got a big long column D containing times formatted as i.e. 13:30. I am
>> trying to do a countif function for finding the count of inputs between 6
>> am
>> and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of
>> using logic => and < within the countif formula... appreciate any help
>> here..
>>
>> .
>>
 | 
Pages: 1
Prev: Days in Text to Dates SPECIAL
Next: Vlookup & relative cell reference?