Summing based on a specifc value accross multiple sheets within a single workbook

[Mvaldesi]

Good afternoon Gurus!

I’ve got a list of reports that each source from various data sources via Access ODBC connections; I’ve been tasked with documenting each of these reports and which fields from which tables they use, and the attached template is the direction I’m headed. As you’ll be able to see, each tab is a different table with the field names across the top, then I list the reports to the left and simply put an “x” under the fields each report uses and count the “x”s for a total number of uses each table has. What I’d like is a master tab at the front of the workbook where I can list each report again, but with the total number of uses they have from the given resource that houses the individual tables. Hope that makes sense? I essentially want to VLOOKUP each report across all tabs within the workbook and sum their totals from the “Field Count” field (column B on each tab) so anyone can open the template and see that the “Q2 POMs and Beyond” report uses 23 fields from the DALA resource; is that feasible and (importantly) in such a way that I can also add more tabs as I find more tables?

[PaulM100]

You need to return all sheets names so, go to Formulas - Define Name - name them Sheets and add this formula: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Then you can use this formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A4:A100"),A3,INDIRECT("'"&Sheets&"'!"&"B4:B100")))

Increase your range as necessary. I've set them to 100 for now.

[Mvaldesi]

Thank you for the response!

I'm sorry; I'm a little confused. What exactly do you mean by "Define Name - name them Sheets"?

[PaulM100]

Here you go:

https://www.howtoexcel.org/tips-and-...k-without-vba/

[Mvaldesi]

Thanks again!

So, using the instructions on your link, I managed to get an index of my sheet names (a nifty trick to be sure!), but I'm failing to understand how this gets me to my desired end result? I want the first sheet to be an index of my unique report names (column A on each sheet,) then sum the total number of fields each report uses. I.e., in my sample workbook attached on the first post, DALA (workbook name) is the data resource. In DALA, I've got 11 tables (unique tabs within the workbook.) The report name is "Q2 POMs and Beyond," and it uses 23 total fields collectively from all the DALA tables. This is the view I'm trying to achieve for each report (Sheet1 is a mock up of the end result.)

[PaulM100]

I've corrected my second post #2. Follow the instructions in the link then use the defined name Sheets in my second formula. Basically, it counts the field per criteria from each sheet and returns the value. Give it a try and if is not what you are looking for, then let us know.

[Mvaldesi]

Ok, I think I made some headway; thank you so much for your help so far! (I'd give you more rep, but the site insists I spread it around before giving it to you again!)

If you can, please take a look at the attachment. I won't claim to understand what exactly the formula is doing, but I followed your steps methodically, and got the expected results for the first report listed (in cell A3.) However, when I drag the formula down to B4, I receive a "circular reference" error message and the value from cell B3 is simply repeated. Stranger still, when I drag the formula down further to B5, I still get the circular reference error, but when I change the SUMIF criteria to cell "A6" I get the expected result for the report below in A6. I'm hoping this is an easy fix! Thanks in advance!

[Mvaldesi]

Ok, I think I might have a guess at what might be going on; when I ‘defined’ the name, I believe it’s including the sheet on which I’m trying to show the counts, so putting this formula in A:B created a circular reference. When I move the formula to C:D, it works!

Thanks again for your help; marking this thread SOLVED (for now…)