Conditional Countif

  • Thread starter Thread starter TFMR
  • Start date Start date
T

TFMR

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
 
With A1:A23 representing time (Containing no or similar dates):

For 8am - 8pm
=+SUMPRODUCT((A1:A23>VALUE("08:00"))*(A1:A23<=VALUE("20:00")))

For 8pm - 8am
=+SUMPRODUCT((A1:A23<=VALUE("08:00"))+(A1:A23>VALUE("20:00")))
 
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"))
 
where I have to write "startup"?

Faraz A. Qureshi said:
With A1:A23 representing time (Containing no or similar dates):

For 8am - 8pm
=+SUMPRODUCT((A1:A23>VALUE("08:00"))*(A1:A23<=VALUE("20:00")))

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

Faraz
Click to expand...
 
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:A23>VALUE("08:00"))*(A1:A23<=VALUE("20:00"))*(UPPER(B1:B23)="STARTUP"))

For 8pm - 8am
=+SUMPRODUCT(((A1:A23<=VALUE("08:00"))+(A1:A23>VALUE("20:00")))*(UPPER(B1:B23)="STARTUP"))
 
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?
 
Back
Top