I've got data organised in the following way: 2 first columns are identifiers and the rest of the columns are values by year (a year per column). So I need to summarise it in a different table based on 2 identifiers an by year. So each value in the summary table should match the combination of two identifiers in the first two columns and organaised by year. There could be multiple rows matching the combination of the identifiers thus using the SUMPRODUCT formula:
But ideally I would like to capture the last array with the data based on the year rather hardwire it into the formula as it is now. I tried to use INDEX/MATCH in the last part but it does not work.
Also, SUMPRODUCT seems to be quite heavy on resources so I wonder if it's possible to replace it with something less resource hungry?
Excel Formula:
=SUMPRODUCT((--(FS!$B$4:$B$150=CF!$A5)),(--(FS!$C$4:$C$150=CF!$B5)),FS!G$4:G$150)But ideally I would like to capture the last array with the data based on the year rather hardwire it into the formula as it is now. I tried to use INDEX/MATCH in the last part but it does not work.
Also, SUMPRODUCT seems to be quite heavy on resources so I wonder if it's possible to replace it with something less resource hungry?
