Sumifs - Lookup from data table to individual tabs

[Database Dummy]

I'm trying to use a sumifs formula to pull data from multiple tabs into one data table. One of my criteria is the name of the tab in my data set. However, I cannot seem to pull the data that I want to from the individual tabs. I have over 20,000 records divided by 16 tabs so I want to automate the process. Any ideas???? I'm at wits end!

 

[Joe4]

Welcome to the Board!

I have over 20,000 records divided by 16 tabs so I want to automate the process. Any ideas????

One of the reasons this is difficult is because you are probably using the wrong tool for the job. When you have an environment like one that you are describing, Excel is not the best tool for this. It sounds like what you really have is a database, and as such, database programs like Access, SQL, and Oracle are better suited for these type of scenarios (relational databases).

I am not saying that it cannot be done in Excel, but all things equal, it would be a whole lot easier to use a normalized database.

If your issue is referencing the other worksheets in your SUMIFS formula, take a look at using the INDIRECT function in conjunction with it (see here: http://www.excelforum.com/excel-wor...rmula-with-indirect-function-explanation.html).

 

[Trevor G]

I'm trying to use a sumifs formula to pull data from multiple tabs into one data table. One of my criteria is the name of the tab in my data set. However, I cannot seem to pull the data that I want to from the individual tabs. I have over 20,000 records divided by 16 tabs so I want to automate the process. Any ideas???? I'm at wits end!

Welcome to the forum,

Can you show some of your data that can be replicated. A little more detail with some example will help find a solution. Joe is right that this sounds as though you would be better off using Access but if the data is already being manipulated lets see what we can do.

You can copy and paste the data into your thread or use one of the suggested converts to get the data into your thread.

 

[Database Dummy]

Thank you both so much! The link to the suggestion did help. In fact, I was able to set up the named tab range and insert it into the SUMIFS within the SUMPRODUCT formula. It produced a result!

However, I then tried to add one more criteria in the formula to lookup to the tab name within the named range based on the [@xxxxx] reference of my data set, but it produces a #VALUE.

In other words, instead of producing a value based on the SUMPRODUCTS of each specific cell within the name range, I was hoping the criteria I set in the SUMIFS would lookup to only 1 tab name instead of all 15.

Here's the example:

1) Each of 15 tabs has a metric name in B17. I17:V17 is the monthly data.
2) On each tab, I12:I12 is the date formatted as MMMMYY (January 2011)
3) The specific values in I17:V17 vary from tab to tab.
4) In my data table I have a SUMIFS formula that says, SUMIFS('TabNAme'!I17:V17,'TabNAme'!I12:V12,[@Date] (where the date matches the month)

This does work, but I want to make it dynamic enough that the tab name can change with the name in my data set (ie. [@TabName]. Furthermore, I want to be able to use the same formula but be able to change the sum range from I17:V17 to a dynamic range because I want to pull from other rows below as well.