Need countifs forumla to analize in a time range

[coil222]

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

Thanks

 

[joeu2004]

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,"<"&TIME(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=360)*(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.

 

[coil222]

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 vi
google

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

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

=COUNTIFS(xdo!A2:A2000,"<"&TIME(6,0,0),xdo!K2:K2000,"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 200

compatibility, try:

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

=COUNTIFS(A1:A1000,">="&TIME(6,0,0),A1:A1000,"<"&TIME(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=360)*(F1:F1000="K"))

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

Note: I assume K1:K1000 contains __numbers__ like 360. If they ar
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 anythin

about PTs. I don't like them because they are more difficult to modif
and
reference