Cascading SUM INDEX INDIRECT MATCH MATCH function in Google Sheets (sample workbook included)

I'm attempting to write a function in google sheets that sums an INDEX INDIRECT MATCH MATCH value across multiple sheets - essentially the total number of hours each employee has allocated towards each client.

I took a first stab at it in cell D2 but I feel like there's gotta be a much more elegant way of writing it rather than copying the current function, adding a "+", then adjusting the tab name reference - especially bc the sheet list is growing by the week so it would be especially nice to auto-update as current weeks are added.

Please see the sample sheet here: https://docs.google.com/spreadsheets/d/1qEAJ1UcNQPD-doUaD3kfZgpDz2phpcXHUJWpVE01I10/edit?usp=sharing

(all sensitive names and clients have been replaced with dummy data)

Thanks so much for your help!

=IF(IFERROR(INDEX(INDIRECT("'"&$B2&"'!"&$A$3),MATCH('client allocation'!E$1,INDIRECT("'"&$B2&"'!"&$A$4),0)),"")=0,"",IFERROR(INDEX(INDIRECT("'"&$B2&"'!"&$A$3),MATCH('client allocation'!E$1,INDIRECT("'"&$B2&"'!"&$A$4),0)),""))  

https://stackoverflow.com/questions/66620068/cascading-sum-index-indirect-match-match-function-in-google-sheets-sample-workb March 14, 2021 at 08:56AM