Possibilities of Accumulating daily Totals/Updates into Summary

[Ravindran Raman]

Good day All.

I have attached my daily report format. Data has to be entered from manually prepared reports into daily dated worksheets. What I would like to do is to have my daily updated reports to be updated in the summary sheet as accumulated total but there will 3 kinds of formulas needed as highlighted in colour -

1. Yellow - Quantity is fixed and totals to be accumulated on daily basis
2. Green - Non fixed Quantity, where the quantity can either Add or MINUS depending useage
3. Grey - Quantities to be added on daily basis when updating the daily report

Please review the worksheet and advise if its possible to work with formula and how i could start working on the formula.

Thank you

[Bo_Ry]

Please edit Summary column B to match header in Sheet 1.1.19 column A

And try at Summary
C8 drag across and down.
=INDEX(Table1[C12],MATCH($B8,INDEX(Table1,,1),))

L8 drag across and down.
=INDEX(Table2[C12],MATCH($B8,INDEX(Table1,,1),))

[Ravindran Raman]

Hi Bo, Thank you. I have corrected the headings to match the exact text and have inserted both the formula but it could only update the total from 1.1.19 but not the accumulated total up to sheet 8.1.19. The total that should have sum in the summary for B8 is 11940 have also tried the below formula but am really confused.

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria, INDIRECT("'"&sheets&"'!"&"B8")))

[Bo_Ry]

Please try at B8 drag across and down

=SUMPRODUCT(SUMIFS(INDIRECT("'"&ROW(INDIRECT("1:8"))&".1.19'!C",),INDIRECT("'"&ROW(INDIRECT("1:8"))&".1.19'!A:A"),$A8))

I remove extra space at sheet name "7.1.19 " to "7.1.19"


For INDIRECT("'"&sheets&"'!"&"rng") , you need to create name range that contents all your sheet name
which I use
"'"&ROW(INDIRECT("1:8"))&".1.19'" for Sheet
and "C" and "A:A" for "rng" instead

[Ravindran Raman]

Dear Bo. Thank you very much. Your formula worked well. I update the sheets till 31.1.19 and have just changed the date on the formula as below. On entry of the total in 31.1.19 sheet, record is captured on the summary.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&ROW(INDIRECT("1:31"))&".1.19'!C",),INDIRECT("'"&ROW(INDIRECT("1:31"))&".1.19'!A:A"),$A8))

Again, Thank you Very Much.