Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Countif | Excel Worksheet Functions | |||
Conditional Countif | Excel Worksheet Functions | |||
conditional COUNTIF | Excel Discussion (Misc queries) | |||
Conditional Countif | Excel Worksheet Functions | |||
Conditional countif | Excel Worksheet Functions |