[Solved] Meal Tracker in Excel for Different Classes

[shamimarita]

Hello Lisa,

Great progress and thanks for sharing the exact formula and the switch from R to U. Since it’s still not calculating after widening the ranges, here are the two most common gotchas when adding Saturday:
Make sure the two criteria in each SUMIFS are still matching real values in the new Saturday column:

• 'Week 1 (Class x)'!$D$3:$U$3 must contain real dates (not text). In the Grand Total sheet, the $A7 cell must also be a real date.
• Quick test: =ISNUMBER($A7) should return TRUE, and =ISNUMBER(INDEX('Week 1 (Class 1)'!$D$3:$U$3,1, COLUMNS('Week 1 (Class 1)'!$D$3:$U$3))) should also be TRUE for the Saturday column.
• 'Week 1 (Class x)'!$D$6:$U$6 must contain the exact code you filter by. Your formula uses "SN-1". If the new Saturday column header in row 6 isn’t exactly "SN-1" (for example it’s blank, "Saturday", or "SN-6"), SUMIFS will return zero. Match the text exactly (no extra spaces).

If this doesn't solve your issue, you can upload your current Excel file.

 

[LGordon]

Hello Lisa,

Great progress and thanks for sharing the exact formula and the switch from R to U. Since it’s still not calculating after widening the ranges, here are the two most common gotchas when adding Saturday:
Make sure the two criteria in each SUMIFS are still matching real values in the new Saturday column:

• 'Week 1 (Class x)'!$D$3:$U$3 must contain real dates (not text). In the Grand Total sheet, the $A7 cell must also be a real date.
• Quick test: =ISNUMBER($A7) should return TRUE, and =ISNUMBER(INDEX('Week 1 (Class 1)'!$D$3:$U$3,1, COLUMNS('Week 1 (Class 1)'!$D$3:$U$3))) should also be TRUE for the Saturday column.
• 'Week 1 (Class x)'!$D$6:$U$6 must contain the exact code you filter by. Your formula uses "SN-1". If the new Saturday column header in row 6 isn’t exactly "SN-1" (for example it’s blank, "Saturday", or "SN-6"), SUMIFS will return zero. Match the text exactly (no extra spaces).

If this doesn't solve your issue, you can upload your current Excel file.

I managed to get September's spreadsheet to calculate correctly. Thanks for your help.

It seems I'm always having one kind of problem or another with this spreadsheet so yes, that statement means I have a new problem.

Each month I make adjustments to the classes. I add/delete rows to add/remove students. I always add/remove middle rows so that I maintain the formulas. I do the same thing each month. Most of the time, the grand total page will just calculate. Sometimes one field won't work and I just use a workaround for it using columns to the right and then add/subtract for the total to get my numbers to match. For this month (October) the first column of the grand total seems to be doubling the numbers. The Week totals are all correct. It's just the grand total page. I've attached this month. On the Grand total page, I highlighted all the totals that don't match the weekly totals page. It is quite a lot and considering no changes have been made to any formulas, I'm not quite understanding why the totals seem to be almost doubled. Can you help?