Building a summary table across multiple worksheets (SUMIF or VBA?)

Hello,

The attached spreadsheet builds upon an item that was solved here: http://www.excelforum.com/excel-gene...ell-above.html

In my workbook, the worksheet called SUMMARY needs to collect, from numerous worksheets, the totals that are found in Rows 3-15 on every relevant worksheet. Those rows have the same Share identifiers (S1.1, S2.4, etc.). The column entries in those Rows 3-15 vary in their start position (first column containing good data) and in the number of columns containing data. Each column has a heading in the format ITEM A, ITEM B, etc. (fake item names). In some cases the same ITEM will repeat in multiple columns (though not in the example attached) and each instance needs to be captured.

On the SUMMARY worksheet, the ITEMS A-N are listed in rows and I need to return in separate columns the breakdown based on the Share identifier.

For example: SUMMARY worksheet, Row 13 is for ITEM E; its cumulative quantity is 1881 (cell K13). I need to return at cell P13 (under S1.1) the total, across all relevant worksheets, for quantities in Row 7 where the column heading (Row 3) equals ITEM E. So it would be WSHEET1 cell L5 plus WSHEET2 (row 5, no column matches) plus WSHEET3 (row 5, no column matches), etc. In other words it would add up every Row 5 entry on every worksheet where the column heading is ITEM E. The formula would be entered in SUMMARY cell P9 and copied down P9 to P22. --> Then similar formulas would be constructed for all the yellow-highlighted fields in the SUMMARY worksheet.

Above I say that all "relevant worksheets" are to be added. What I mean by that is in my real workbook there are about 30 worksheets to be summed (identified by same tab color) and 10-15 other worksheets (not-relevant) that do not contain info to be summed. However, I don't believe the not-relevant worksheets would foul up a formula that were applied to ALL worksheets (since they don't have text that would match a SUMIF). That said, ideally the formula would only target the relevant, color-coded worksheets.

It seems like a SUMIF across the multiple relevant worksheets is what I need but not sure how to construct. But any solution would be great - whether a formula solution or a VBA solution (such as a button-macro) to step through a group of worksheets and collect/paste the info into SUMMARY? This is the last step in my workbook!

Thank you very much for any thoughts,

Steve

I looked further into my own question/request. The below formula, pasted into all the yellow highlighted cells on the SUMMARY will do the math I need for one sheet.

=SUMIF('WSHEET 1'!$C$3:$AZ$3,CONCATENATE("ITEM ",$H9),INDEX('WSHEET 1'!$C:$AZ,MATCH(O$8,'WSHEET 1'!$B:$B,0),0))

See updated spreadsheet with the above formula included. This successfully totals WSHEET 1 but of course no other sheets.

My question then boils down to: how do I apply this formula to a selection of multiple worksheets, and have it be flexible so that more sheets can be added or sheets removed?

Maybe if I build a sheet list and use SUMPRODUCT and INDIRECT (speculating based on https://exceljet.net/formula/3d-sumi...ple-worksheets)? I'll start down that path but would love to hear any better/simpler ideas