sum of values between 2 time band

[tabkaz]

I some how managed to solve myself after searching & looking and finally got help through one of the DonkeyOte old post.


i have a formula in my sheet which works well for time band beginning with a smaller time band & ends with a larger time band.

i can have a sum based for time value between 8:00 PM to 11:00 PM but if values ranges between 8:00 PM to 1:00 AM it gives 0 values.

i tried with couple of formula but both couldn't calculate the desired result.

=SUMIFS(AK9:AK39,A9:A39,">="&W42,A9:A39,"<"&Z42)

=SUMPRODUCT(--($A$3:$A$10>=E12), --($A$3:$A$10<F12), $K$3:$K$10)

can any one look into the attached sheet. i have put expected result along with time band values.

[NeedForExcel]

Hi,

Give this a try -

Please Login or Register  to view this content.

[NeedForExcel]

I am sorry, that might returning incorrect answer.. Can you let me know if the formula is OK?

[Sam Capricci]

how about this =SUMIFS(B9:B15,A9:A15,">=8:00:00 pm",A9:A15,"<=11:00:00 pm") ?

[tabkaz]

I have chked but its not working when i change time band. for example if i change time to 17:00 to 01:00 its showing 56 sum whereas it should include all the values because its covering all time slot so sum must be 96.

[tabkaz]

for time range between PM to PM it works fine but if change to PM to AM it could not calculate the sum. the formula u posted is already being used by me but for time range of PM to PM.