Vlookup and Sum Across Multiple Tabs?

[Dean99]

Hi Everyone-

I currently have a tax workbook with 50+ tabs (at least one for each State), and in each tab there are tables with many divisions and the amount of taxes paid. I'm trying to figure out a way to do a vlookup and sum in a main summary worksheet such that I can get the total taxes paid by each division (it would need to vlookup each of the 50+ tabs, and then sum). Is there a way to do this? I'm not very good with VBA so please be gentle if that's what is required . . .

Thanks in advance for your help!

 

[Rominall]

Ok, I tried the formula you provided and I get a #REF! error. Here's the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!b55:b200"),B4,INDIRECT("'"&list&"'!c55:c200")))

my list is AA3:AA54

What I'm looking up is dates in colum b to match the dates on individual worksheets and then add values in column c (yes my data starts on each worksheet on row 55).

What's wrong?

 

[gheyman]

You can make this simple and not get fancy. In your Main Summary tab add 50 columns to the left of your division. put your Lookup for each tab/state in each of the new columns and then add a sum column that adds them together. (You can hide the 50 columns if the sheet gets too big or you just dont want to see them)

 

[Aladin Akyurek]

Ok, I tried the formula you provided and I get a #REF! error. Here's the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!b55:b200"),B4,INDIRECT("'"&list&"'!c55:c200")))

my list is AA3:AA54

What I'm looking up is dates in colum b to match the dates on individual worksheets and then add values in column c (yes my data starts on each worksheet on row 55).

What's wrong?

Do all the sheets in list available?

 

[Rominall]

Well maybe - each sheet is a person and a person could have a starting date of 12/XX/2013 or 3/1/2014. Each sheet's data starts on row 55, column b contains the dates, column c starts the data for that date (I actually want to sum all the rest of the columns but thought I'd start with the frist one). I decided it might be a sheet name issue so I copied the sheet names into the LIST range just to make sure.

On the summary sheet b3 starts the dates (earliest to latest) and c3 is where I need my first formula.

 

[Aladin Akyurek]

Well maybe - each sheet is a person and a person could have a starting date of 12/XX/2013 or 3/1/2014. Each sheet's data starts on row 55, column b contains the dates, column c starts the data for that date (I actually want to sum all the rest of the columns but thought I'd start with the frist one). I decided it might be a sheet name issue so I copied the sheet names into the LIST range just to make sure.

On the summary sheet b3 starts the dates (earliest to latest) and c3 is where I need my first formula.

I meant Are, not Do... If any sheet in list is not available, you'll get a #REF! error.

Otherwise, control+shift+enter, not just enter:

=SUM(IFERROR(SUMIF(INDIRECT("'"&list&"'!b55:b200"),B4,INDIRECT("'"&list&"'!c55:c200")),0))

 

[Rominall]

I meant Are, not Do... If any sheet in list is not available, you'll get a #REF! error.

Otherwise, control+shift+enter, not just enter:

=SUM(IFERROR(SUMIF(INDIRECT("'"&list&"'!b55:b200"),B4,INDIRECT("'"&list&"'!c55:c200")),0))

SUCCESS!!!!! THANKS!!!!! BTW - do you know if any of the statistical formulas are not support across worksheets? I have some avg, med, mean etc. calculations to do as well.

 

[Rominall]

Ok, that was a stupid question. I'm gonna have to go at some of those formulas in a different way. Thanks for all your help.

 

[Aladin Akyurek]

SUCCESS!!!!! THANKS!!!!! BTW - do you know if any of the statistical formulas are not support across worksheets? I have some avg, med, mean etc. calculations to do as well.

Ok, that was a stupid question. I'm gonna have to go at some of those formulas in a different way. Thanks for all your help.

You are welcome. For averaging, you need to call SumIf and CountIf. Not all stats will be this easy though.