The below steps will demonstrate one method to carry out your work. Use it on a blank Excel file, so that you understand how it works, and then use it on your main file.
I have assumed 'Acct' to be in cells D1 to D10 and 'Amount' to be in G1 to G10, in each sheet from Sheet1 to Sheet 5.
In column I, of Sheet1, list the items present in column D (Acct) of all the sheets. Remove duplicates.
Select Sheet1. With Shift key pressed, select Sheet5. All sheets from Sheet1 to Sheet5 will get selected. Whatever you do on 1 sheet, will happen on all the sheets.
Select cells J1, enter the following formula
=SUMIF($D$1:$D$10,$I1,$G$1:$G$10)
Drag (or paste) the formula in cells J1to J10. It will get pasted in cells J1 to J10, in all sheets.
Click the tab of any sheet. Now your selection of all sheets is deactivated.
In cell K1 of sheet1, enter the below formula
=SUM(Sheet1:Sheet5!J1)
This formula is a 3D formula and will add cells J1 in all sheets from Sheet1 to Sheet5.
Do let me know if this is what you wanted to do, or if you need any clarification.
Regards,
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India
