Hello,
I have 22 sheets in a workbook, each with dates across the top row and metrics down column A. The metrics are things like "Revenue A, Revenue B, Expense A, Expense B" and each worksheet has the same metrics but in different orders. The dates also do not all start at the same time so are not in the same columns on each worksheet (but are in the same row, 3). I'm trying to combine them into a master sheet that adds data from each sheet based on the date and metric, so I need both a horizontal and vertical lookup, summed across sheets. Each sheet has been renamed, and I pulled the sheet names into a table named "sites" so that I can reference them using INDIRECT. "sites" is a named range, the range is all the tab names. I've tried the following formulas:
=SUMPRODUCT(INDEX(INDIRECT("'"&sites&"'!$A$3:$G$7"),MATCH($A4,INDIRECT("'"&sites&"'!$A$3:$A$7"),0),MATCH(B$3,INDIRECT("'"&sites&"'!$A$3:$G$3"))))
=SUMPRODUCT(SUMIFS(INDIRECT("'"&sites&"'!$A$3:$G$7"), INDIRECT("'"&sites&"'!$A$3:$G$3"), B$3, INDIRECT("'"&$sites&"'!$A$3:$A$7"), $A5))
SUMIFS doesn't seem to work with both row and column criteria (#VALUE error). The Index match match formula seems a bit more promising - it works when I look at a single sheet, but will not sum across sheets (it gives a #VALUE error too).
Any thoughts/advice is greatly appreciated!
Thanks,
B