I am trying to extract the total of certain lines and certain columns from a big P&L by department spreadsheet, I am currently using this formula:
=(SUMPRODUCT(("Revenue"='[Mapping and PL-BS inputs.xlsx]ProfitandLoss'!$A$11:$A$300)*("Rental Income"='[Mapping and PL-BS inputs.xlsx]ProfitandLoss'!$B$11:$B$300)*(INDEX('[Mapping and PL-BS inputs.xlsx]ProfitandLoss'!D$11:AZ$300,,MATCH("DEPARTMENT NAME",'[Mapping and PL-BS inputs.xlsx]ProfitandLoss'!D$7:AZ$7,0))))/1000)
Revenue being the top classification, rental income being the sub-classification, I want to be able to do this for multiple department names (column headings) within the same sumproduct, not just doing +(the whole formula again).
I have tried using an array in place of department name, but I couldn't get that to work.
Thanks for your help.
Bookmarks