I'm trying to create an Excel formula that is able to sum multiple rows in a table, where the rows and column to be summed are determined by the contents of other cells.
Ordinarily I would use Index Match Match to achieve this, but the multiple rows summation has left me stumped.
I've seen a couple of examples on here of Index Match with a SUMIFS formula, but nothing that pairs this with Index Match Match.
I have two tables on different Excel sheets. The first one looks a little this (the actual table is 105 columns x 200 rows):
That is from a sheet called "Firm Cost Summary". Row 4 contains a list of unique employee numbers. Column A is the expense category per our accounting system and Column B is a broader category that should be used in Excel to group similar items. Column E onwards then contains the numerical information to be aggregated.
What I would then like to do is summarise that table in a more presentable format that can then be manipulated in other ways. The table looks like this:
That is on a sheet called "Staff Cost Summary". I would like to fill out the info in the yellow cells, i.e. total the salary, bonus, benefits, etc, of each staff member. Ideally this would be a formula I input in cell E6 that I can then drag right and downwards to fill the table.
To give an example, to fill out cell I6 in the second table, the formula should look in cell A6 to find the employee number (1 in this case) and look this up in row 1 of the first table to find the appropriate column of the first table (column E in this case).
The formula should then look in cell I5 of the second table to see that we are looking to aggregate benefits, then look down column B of the first table to find each row that should be summed (rows 7-10 in this case).
With that in mind, here's what I've got:
=INDEX('Firm Cost Summary'!$A$4:$G$10,MATCH('Staff Cost Summary'!$A6,'Firm Cost Summary'!$A$4:$G$10,0),MATCH('Staff Cost Summary'!E$5,'Firm Cost Summary'!$B$4:$B$10,0))
Total benefits for Joe Bloggs are the sum of cell E7:E10 of table 1, i.e. 5 + 10 + 50 + 100 = 165.
Clearly there are multiple matches in column B of that table, so the above formula gives an answer of 0. Any ideas how I can tweak that to make it work?
