Excel not calculating columns correctly with a formula

[Bar]

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.

 

[shamimarita]

Hello Bar,

Welcome to the ExcelDemy Forum! Thank you for explaining your issue and sharing your spreadsheet.

I reviewed the formulas you provided and noticed a few things that may explain the inconsistent weekly totals.

1. Correct the formula in column G (Total Hours)
Your current formula uses A5 to calculate the start and end of the week, even though the IF condition checks whether A6 is Sunday.

If A5 contains Saturday's date, your formula excludes Sunday's hours from the weekly total.

• Try this corrected formula in G6:

=IF(WEEKDAY(A6,2)=7,SUMIFS($C$2:$C$233,$A$2:$A$233,">="&A6-6,$A$2:$A$233,"<"&A6+1),"")

2. Update the formula in column H (Total Cost)
Your column H formula uses the correct row reference, but I recommend using the same date criteria and consistent data ranges.

• In H6, enter:

=IF(WEEKDAY(A6,2)=7,SUMIFS($E$2:$E$233,$A$2:$A$233,">="&A6-6,$A$2:$A$233,"<"&A6+1),"")
3. How these formulas work

• WEEKDAY(A6,2)=7 checks whether the date is Sunday.
• A6-6 identifies Monday of that week.
• ">="&A6-6 includes records starting Monday.
• "<"&A6+1 includes all records through Sunday, even if the dates contain time values.
• SUMIFS adds the corresponding hours or costs for the seven-day period.

4. Verify the results

After updating both formulas, copy them down their respective columns and compare the results with your AutoSum totals in columns F and I.

Also, make sure column A contains valid Excel dates and columns C and E contain numeric values. If your dataset extends beyond row 233, update the ranges accordingly.

These corrections address the formula inconsistencies, although I cannot confirm the exact totals without examining the workbook's data.