VLOOKUP and SUM on Multiple Tabs

[jt_tj_ca]

I see the following formula floating around quite a bit:

=SUM(VLOOKUP($A2,May!$A:$K,5,0),VLOOKUP(Sheet2!A2,June!A:K,5,0),+(VLOOKUP(Sheet2!A2,July!A:K,5,0)))

What if you have too many tabs? For instance what if you need a tab for every day of the month?

Is there a condensed version of this formula? Or would an array formula be the way to go?

Thanks,

J.

[NBVC]

Create a column of tabnames somewhere in the sheet, then go to insert|Name|Define and name the range, something like: TabNames

Then apply formula:

=VLOOKUP(A$2,INDIRECT("'"&INDEX(TabNames,MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),A$2)>0,0))&"'!A1:K100"),5,0)

Note that you can't/shouldn't use full column references, so adjust ranges to suit and then confirm with CTRL+SHIFT+ENTER not just ENTER.

[jt_tj_ca]

That works to a point, however it doesn't seem to sum the result from each of the tabs. Perhaps I've missed something. For instance on Tab - 1 the cell being referenced has 10.50 in it. On Tab - 2 that same cell has 4.95 etc etc, instead of summing each of the results from each of the tabs, its only giving me the result from Tab - 1.

Here is the formula I used:

VLOOKUP(A3,INDIRECT("'"&INDEX(Tabnames,MATCH(TRUE,COUNTIF(INDIRECT("'"&Tabnames&"'!A1:A100"),A3)> 0,0))&"'!A1:P100"),16,0)

[DonkeyOte]

To aggregate:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A1:A00"),A3,INDIRECT("'"&TabNames&"'!E1:E100")))

note the above is Volatile and Tabnames must contain only valid sheet names else error shall result

(obviously given SUMIF it will aggregate should more than 1 record exist on any given sheet - unlike VLOOKUP which would retrieve only the first record)

[anne.king]

I have a similar situation, where I am trying to sum accross multiple tabs. I've used the sumproduct formula above, but result = #REF!

Summary Tab: Column A = project Name
1 tab for each employee: Column A = project name; Column J = hours
On summary tab, I want to sum hours by project across all employee tabs

TABNAMES is a Defined Name referencing all tab names in workbook


My formula: =sumproduct(sumif(indirect("'"&TABNAMES&"'!A1:A30"),A3,indirect("'"&TABNAMES&"'!J1:J30")))

What am I missing?

[protonLeah]

anne.king,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.