Prev: If Function Function
Next: Is there a way to conditionally hide rows in Excel?
From: gregv7 on 6 May 2010 17:46
I have a spreadsheet that has the following:
A B C
Start Time Run Day Run Time
11/14/09 12:20 AM Sat 12:20 AM
11/15/09 3:30 PM Sun 3:30PM

The cells are formatted as:
A= Date & Time
B=TEXT(A2,"ddd")
C=A2(cell reference with a time format)
My goal is to count how many times the event occurred on a particular day of
the week and count how many times it occurred during a time frame. EG Monday
between 5:00 AM & 8:00 AM
I have the count for the days of the week, I just can't get the occurrences
between the hours I need.
Thank you in advance!
From: T. Valko on 6 May 2010 18:43
Use cells to hold the criteria...

E2 = some day of week like Mon
F2 = lower time boundary like 5:00 AM
G2 = upper time boundary like 8:00 AM

=SUMPRODUCT(--(B2:B100=E2),--(C2:C100>=F2),--(C2:C100<=G2))

--
Biff
Microsoft Excel MVP


"gregv7" <gregv7(a)discussions.microsoft.com> wrote in message
news:12CB5804-A269-4D67-A377-177F32EAB814(a)microsoft.com...
>I have a spreadsheet that has the following:
> A B C
> Start Time Run Day Run Time
> 11/14/09 12:20 AM Sat 12:20 AM
> 11/15/09 3:30 PM Sun 3:30PM
>
> The cells are formatted as:
> A= Date & Time
> B=TEXT(A2,"ddd")
> C=A2(cell reference with a time format)
> My goal is to count how many times the event occurred on a particular day
> of
> the week and count how many times it occurred during a time frame. EG
> Monday
> between 5:00 AM & 8:00 AM
> I have the count for the days of the week, I just can't get the
> occurrences
> between the hours I need.
> Thank you in advance!


 | 
Pages: 1
Prev: If Function Function
Next: Is there a way to conditionally hide rows in Excel?