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.
|