I am not well versed in Excel ( I do try hard though). So I'm looking to you, the experts, for some knowledge and assistance.
As you can see in the attached spreadsheet, I'm getting inconsistent results using formulas for end of week calculations of both hours and costs. Not sure why.
In column G, for total hours for a 7 day week starting Monday and ending Sunday.
I'm using
=IF(WEEKDAY(A6, 2) = 7, SUMIFS(C$2:C$232,A$2:A$232, ">=" & A5 - WEEKDAY(A5, 2) + 1,A$2:A$232, "<=" & A5), "")
In column H, I'm using
=IF(WEEKDAY(A6, 2) = 7, SUMIFS(E$2:E$233,A$2:A$233, ">=" & A6 - WEEKDAY(A6, 2) + 1,A$2:A$233, "<=" & A6), "")
When I found my figures (using the formula) in column G and column H were incorrect I did a test column using AutoSum to proof my figures for the same 7 day period (columns F and I). The values in column F and column I are correct.
The values in column J and K work out correctly.
I would rather not have to use AutoSum for every week, but I want my formula to give correct data.
I want to be able to trust the figures I'm getting by using the formulas. Right now I don't have that comfort level in the data.
I am hoping you can help me correct my formula.
Thanks in advance.
As you can see in the attached spreadsheet, I'm getting inconsistent results using formulas for end of week calculations of both hours and costs. Not sure why.
In column G, for total hours for a 7 day week starting Monday and ending Sunday.
I'm using
=IF(WEEKDAY(A6, 2) = 7, SUMIFS(C$2:C$232,A$2:A$232, ">=" & A5 - WEEKDAY(A5, 2) + 1,A$2:A$232, "<=" & A5), "")
In column H, I'm using
=IF(WEEKDAY(A6, 2) = 7, SUMIFS(E$2:E$233,A$2:A$233, ">=" & A6 - WEEKDAY(A6, 2) + 1,A$2:A$233, "<=" & A6), "")
When I found my figures (using the formula) in column G and column H were incorrect I did a test column using AutoSum to proof my figures for the same 7 day period (columns F and I). The values in column F and column I are correct.
The values in column J and K work out correctly.
I would rather not have to use AutoSum for every week, but I want my formula to give correct data.
I want to be able to trust the figures I'm getting by using the formulas. Right now I don't have that comfort level in the data.
I am hoping you can help me correct my formula.
Thanks in advance.