Conditional Countif
 

Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards

Conditional Countif
 

With A1:A23 representing time (Containing no or similar dates):

For 8am - 8pm
=+SUMPRODUCT((A1:A23VALUE("08:00"))*(A1:A23<=VALU E("20:00")))

For 8pm - 8am
=+SUMPRODUCT((A1:A23<=VALUE("08:00"))+(A1:A23VALU E("20:00")))
--
Thanx in advance,
Best Regards,

Faraz


"TFMR" wrote:

Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards

Conditional Countif
 

If your data is arranged in this manner;

Column A Column B
Date&Time Status
12/31/2009 7:09 Startup
12/31/2009 7:39 Startup
12/31/2009 8:09
12/31/2009 8:39 Startup
12/31/2009 9:09
12/31/2009 9:39 Startup
12/31/2009 10:09 Startup
12/31/2009 10:39
12/31/2009 11:09 Startup

'between 8am and 8pm
With no blank cells and date/time in the range A2:A10
=SUMPRODUCT((HOUR(A2:A10-TIME(8,0,0))<12)*(B2:B10="Startup"))

'between 8pm and 8am
With no blank cells and date/time in the range A2:A10
=SUMPRODUCT(--(HOUR(A2:A10-TIME(8,0,0))=12)*(B2:B10="Startup"))

--
Jacob


"TFMR" wrote:

Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards

Conditional Countif
 

where I have to write "startup"?

"Faraz A. Qureshi" wrote:

With A1:A23 representing time (Containing no or similar dates):

For 8am - 8pm
=+SUMPRODUCT((A1:A23VALUE("08:00"))*(A1:A23<=VALU E("20:00")))

For 8pm - 8am
=+SUMPRODUCT((A1:A23<=VALUE("08:00"))+(A1:A23VALU E("20:00")))
--
Thanx in advance,
Best Regards,

Faraz


"TFMR" wrote:

Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards

Conditional Countif
 

OK
Now I get the complete/clear picture.
With A1:A23 representing time (Containing no or similar dates) &
B1:B23 representing startup status:

For 8am - 8pm
=+SUMPRODUCT((A1:A23VALUE("08:00"))*(A1:A23<=VALU E("20:00"))*(UPPER(B1:B23)="STARTUP"))

For 8pm - 8am
=+SUMPRODUCT(((A1:A23<=VALUE("08:00"))+(A1:A23VAL UE("20:00")))*(UPPER(B1:B23)="STARTUP"))

--
Thanx in advance,
Best Regards,

Faraz


"TFMR" wrote:

where I have to write "startup"?

"Faraz A. Qureshi" wrote:

With A1:A23 representing time (Containing no or similar dates):

For 8am - 8pm
=+SUMPRODUCT((A1:A23VALUE("08:00"))*(A1:A23<=VALU E("20:00")))

For 8pm - 8am
=+SUMPRODUCT((A1:A23<=VALUE("08:00"))+(A1:A23VALU E("20:00")))
--
Thanx in advance,
Best Regards,

Faraz


"TFMR" wrote:

Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards

Conditional Countif
 

You have overlapping intervals:

08:00 to 20:00 (8:00 AM to 8:00 PM)
20:00 to 08:00 (8:00 PM to 8:00 AM)

If the time is 8:00 PM which interval should it be counted with?
If the time is 8:00 AM which interval should it be counted with?

--
Biff
Microsoft Excel MVP


"TFMR" wrote in message
...
Hello all,

I have two column one with date & Time and other with status of vehicle, I
want to count how many startup during 08:00 to 20:00 and how many startup
20:00 to 08:00.

Thanks & Regards