I am using Excel 2016 on Windows.
The information from table A1 to F7 will be updated every month.
Basically I want a formula in cell A10 and B10 that calculates automatically the work by Junior officers and Senior officers respectively every time new information is updated from table A1 to F7.
The rank of an officer is shown in the table from H1 to I6.
The requirement is that no helper column should be added in the spreadsheet.
The figure in cell A10 and B10 is currently hardcoded.
A10 = 100+600+250+20+60 which is the work done by Junior officers.
B10 = 200+300+400+150+350+450+650+0+10+50 which is the work done by Senior officers.
I don't have Office 365 so the below does not work.
=SUMPRODUCT((VLOOKUP(INDEX(VSTACK($A$3:$B$7,$C$3:$D$7,$E$3:$F$7),,1),$H$2:$I$6,2,0)=A$9)*(INDEX(VSTACK($A$3:$B$7,$C$3:$D$7,$E$3:$F$7),,2)))
Thanks!