sum across multiple worksheets with data sitting in variable cells

I think I may need to use the sumproduct, sumif and indirect formula but haven't used it before so need someone to explain each element to me.

I have a summary worksheet which I want to sum data from all the other worksheets within the one document. The columns in the worksheets are all in the same place but the data against each row is not necessarily in the same row number on each worksheet. Therefore, I need to be able to easily pull together (without using the link formula and without index/matching every worksheet) the numbers from each worksheet that correspond to the column title and row title and sum within the cell.


Any assistance outlining what each of the formulas actually do would be great so I can remember it for the future. I have tried to attach the file with no luck. If you need me to any tips on this also appreciated.

Put in F18 and copied down and cross

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&{6370,6520,6570,6610,7060,7070,7140,"714A",7260,7400,7520,7570,"757A","757B",7740,7820,6220}&"'!A4:A89"),$A18,INDIRECT("'"&{6370,6520,6570,6610,7060,7070,7140,"714A",7260,7400,7520,7570,"757A","757B",7740,7820,6220}&"'!F4:F89"))),"")

Thank you so much. I really appreciate it. If you have time can you tell me the following:

How do I copy it to the columns on either side. When I try my normal copy actions it won't automatically update according to the data on the other worksheets but instead update with the data that is in 2018YTD Actual.

Also, can you tell me what the symbols within the formula are doing. I am learning Excel mostly via google and this forum so any guidance, if you have time about what each of the symbols do would be greatly appreciated.

I just realised I needed to change the column range within the formula so have done that and all is working well now.

Apologies FDibbins. Yes the columns do change which is why how I was summing in C18 wasn't working.