Need countifs forumla to analize in a time range
 

I have a spreadsheet that I need some help with having a count formula count events between the timeframes 00:00-5:59 AM, 6:00am-1:29pm and 1:30pm-11:59pm, but also while observing that K2:K2000 ="360" and F2:F2000 ="K". I've been playing with some countifs and sumproducts for a while and I'm not getting anywhere. Please help!

Thanks!

Need countifs forumla to analize in a time range
 

"coil222" wrote:
I have a spreadsheet that I need some help with having a count formula
count events between the timeframes 00:00-5:59 AM, 6:00am-1:29pm and
1:30pm-11:59pm, but also while observing that K ="360" and F ="K". I've
been playing with some countifs and sumproducts for a while and I'm not
getting anywhere.

A little light on details. I assume you want to count each time frame
separately. If times are in A1:A1000 and you do not require Excel 2003
compatibility, try:

=COUNTIFS(A1:A1000,"<"&TIME(6,0,0),K1:K1000,360,F1 :F1000,"K")

=COUNTIFS(A1:A1000,"="&TIME(6,0,0),A1:A1000,"<"&T IME(13,30,0),K1:K1000,360,F1:F1000,"K")

=COUNTIFS(A1:A1000,"="&TIME(13,30,0),K1:K1000,360 ,F1:F1000,"K")

If you require Excel 2003, or if the "times" include dates, try:

=SUMPRODUCT((A1:A1000<TIME(6,0,0))*(K1:K1000=360)* (F1:F1000="K"))

=SUMPRODUCT((A1:A1000=TIME(6,0,0))*(A1:A1000<TIME (13,30,0)*(K1:K1000=360)*(F1:F1000="K"))

=SUMPRODUCT((A1:A1000=TIME(13,30,0))*(K1:K1000=36 0)*(F1:F1000="K"))

If the "times" include dates, change A1:A1000 to MOD(A1:A1000,1) to remove
the date part.

Note: I assume K1:K1000 contains __numbers__ like 360. If they are truly
strings like "360", replace 360 with "360" in the SUMPRODUCT formulas.

PS: Using a Pivot Table would be more efficient. I don't know anything
about PTs. I don't like them because they are more difficult to modify and
reference.

Thank you, YES each frame separately. No 2003 support is not needed.

Worked like a charm thank you.
Pasting my formulas here incase anyone else stumbles across this via google

=COUNTIFS(xdo!A2:A2000,"="&TIME(6,0,0),xdo!A2:A20 00,"
<"&TIME(13,30,0),xdo!K2:K2000,"360",xdo!F2:F2000," S")

=COUNTIFS(xdo!A2:A2000,"="&TIME(13,30,0),xdo!K2:K 2000,"360",xdo!F2:F2000,"S")

=COUNTIFS(xdo!A2:A2000,"<"&TIME(6,0,0),xdo!K2:K200 0,"360",xdo!F2:F2000,"S")


A little light on details. I assume you want to count each time frame
separately. If times are in A1:A1000 and you do not require Excel 2003
compatibility, try:

=COUNTIFS(A1:A1000,"<"&TIME(6,0,0),K1:K1000,360,F1 :F1000,"K")

=COUNTIFS(A1:A1000,"="&TIME(6,0,0),A1:A1000,"<"&T IME(13,30,0),K1:K1000,360,F1:F1000,"K")

=COUNTIFS(A1:A1000,"="&TIME(13,30,0),K1:K1000,360 ,F1:F1000,"K")

If you require Excel 2003, or if the "times" include dates, try:

=SUMPRODUCT((A1:A1000<TIME(6,0,0))*(K1:K1000=360)* (F1:F1000="K"))

=SUMPRODUCT((A1:A1000=TIME(6,0,0))*(A1:A1000<TIME (13,30,0)*(K1:K1000=360)*(F1:F1000="K"))

=SUMPRODUCT((A1:A1000=TIME(13,30,0))*(K1:K1000=36 0)*(F1:F1000="K"))

If the "times" include dates, change A1:A1000 to MOD(A1:A1000,1) to remove
the date part.

Note: I assume K1:K1000 contains __numbers__ like 360. If they are truly
strings like "360", replace 360 with "360" in the SUMPRODUCT formulas.

PS: Using a Pivot Table would be more efficient. I don't know anything
about PTs. I don't like them because they are more difficult to modify and
reference.[/quote]