Re: Sum data across sheets based on multiple criteria (row and column)
Very helpful, thank you!
Re: Sum data across sheets based on multiple criteria (row and column)
Very helpful, thank you!
Re: Sum data across sheets based on multiple criteria (row and column)
Hey,
I used the last formula because there will be instances where not all the dates are on all sheets. Those replacements worked! Thank you so much!!!!
Now I just need to figure out what everything means so I can explain this beautiful monster of a formula to my coworkers.. any chance you can say it in words in just a couple of sentences?
Re: Sum data across sheets based on multiple criteria (row and column)
Quote from Domenic;718125Display MoreI haven't looked at your sample workbook, but will the date of interest always occur in each worksheet even though they may not be in the same order? Or will there be situations where one or more worksheets may not contain the date of interest. If the former, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...
=SUM(SUMIF(INDIRECT("'"&sites&"'!A3:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A3:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1)))
Actually, there's no need to include Row 3 in the criteria range and the sum range...
=SUM(SUMIF(INDIRECT("'"&sites&"'!A4:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A4:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1)))
Otherwise, if the latter, try the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...
=SUM(IF(MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6}+1,0),{1;1;1;1;1;1;1})>0,SUMIF(INDIRECT("'"&sites&"'!A4:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A4:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1))))
Hope this helps!
P.S. I've assumed that your named range 'sites' defines a vertical range cells.
Thank you so much! This worked (at least on my sample dataset)! Quick Q so that I can understand this formula and apply to my full dataset.. what do {0,1,2,3,4,5,6} and {1;1;1;1;1;1} do? The example I gave had 6 columns, but the actual data set has 240 columns - will I need to adjust anything?
Thanks!
Bari
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