Sum AM Amount

[Geo Jul]

Dear All

I want to sum for month of January 2023 from time 8 AM till 1 PM (13: 00) Only​

I used the following formula =SUMPRODUCT((Sheet1!$A$4:$A$151>=DATE(2023,1,1)+TIME(8,0,0))*(Sheet1!$A$4:$A$151<=DATE(2023,1,1)+TIME(13,0,0))*(Sheet1!$B$4:$B$151))​

and =SUMIFS(Sheet1!$B$4:$B$151,Sheet1!$A$4:$A$151,">="&DATE(2023,1,1)+TIME(8,0,0),date,"<="&DATE(2023,1,1)+TIME(13,0,0))​

both of then gave me wrong result​

please need your help Best REgards​

 

[skillilea]

Can you post your sheet with XL2BB

 

[Geo Jul]

Can you post your sheet with XL2BB

i am at work we can not use add-ins

 

[skillilea]

try this formula, tough to debug when I don't have the data but, I think this is correct

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1) + TIME(8, 0, 0)), Sheet1!B3:B151,  "<=" & (DATE(2023, 1, 1) + TIME(13, 0, 0)))

 

[Geo Jul]

try this formula, tough to debug when I don't have the data but, I think this is correct

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1) + TIME(8, 0, 0)), Sheet1!B3:B151,  "<=" & (DATE(2023, 1, 1) + TIME(13, 0, 0)))

many thanks for your reply and your time the expected result should read 9373 and your formula gave only date 01/01/2023 and the correct timing 460
I need to sum up if month of January 2023 and the time is from 8:00 AM till 13:00 PM

 

[skillilea]

This will give you the whole month

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1) + TIME(8, 0, 0)), Sheet1!B3:B151, "<=" & (DATE(2023, 1, 31) + TIME(13, 0, 0)))

 

[Geo Jul]

This will give you the whole month

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1) + TIME(8, 0, 0)), Sheet1!B3:B151, "<=" & (DATE(2023, 1, 31) + TIME(13, 0, 0)))

the result was 18511 which is wrong it should read 9373

 

[skillilea]

Yikes, OK.

Add this formula to column D

=IF(AND(B3>=DATE(YEAR(B3), MONTH(B3), DAY(B3)) + TIMEVALUE("8:00"), B3<=DATE(YEAR(B3), MONTH(B3), DAY(B3)) + TIMEVALUE("13:00")), "X","")

lets get the time value to show X if in the range

then use this

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1)), Sheet1!B3:B151, "<=" & (DATE(2023, 1, 31) ), D3:D151, "=X")

 

[Geo Jul]

Yikes, OK.

Add this formula to column D

=IF(AND(B3>=DATE(YEAR(B3), MONTH(B3), DAY(B3)) + TIMEVALUE("8:00"), B3<=DATE(YEAR(B3), MONTH(B3), DAY(B3)) + TIMEVALUE("13:00")), "X","")

lets get the time value to show X if in the range

then use this

=SUMIFS(Sheet1!C3:C151, Sheet1!B3:B151, ">=" & (DATE(2023, 1, 1)), Sheet1!B3:B151, "<=" & (DATE(2023, 1, 31) ), D3:D151, "=X")

Thank you for the formulas It sums all the "X" = 9116 and the sum should read 9373
i sorted all "X" and sum them, and it gave me 9373