# Vlookup and Sum Across Multiple Tabs?

This is a discussion on Vlookup and Sum Across Multiple Tabs? within the Excel Questions forums, part of the Question Forums category; Hi Everyone- I currently have a tax workbook with 50+ tabs (at least one for each State), and in each ...

1. ## Vlookup and Sum Across Multiple Tabs?

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 . . .

2. ## Re: Vlookup and Sum Across Multiple Tabs?

Welcome to the board!

A formula like this would work:
Code:
=SUM(Sheet2!A1,Sheet4!A1)
As long as the total is in the same cell on every sheet

Michael

3. ## Re: Vlookup and Sum Across Multiple Tabs?

Hi Guys-

Thanks for your reply! However, the values I'm summing are now always in the same cell . . . although , they will be in the same column (hence, I was trying to do a vlookup). Is this possible? Thanks!

4. ## Re: Vlookup and Sum Across Multiple Tabs?

Something like that sounds like it would be simpler with Named Ranges:

=Sum(Alabama!SalesTax + Alaska!SalesTax + ... + Wyoming!SalesTax)

Other than that (pretty simplistic) idea, can you give an example of what you're trying to accomplish?

5. ## Re: Vlookup and Sum Across Multiple Tabs?

Originally Posted by Dean99
Hi Guys-

Thanks for your reply! However, the values I'm summing are now always in the same cell . . . although , they will be in the same column (hence, I was trying to do a vlookup). Is this possible? Thanks!
Did you try to look at the link I have posted?
VLOOKUP will not give you SUM from the sheets(unless nested(VLOOKUP()+VLOOKUP()....))

To post data:
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie

or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

6. ## Re: Vlookup and Sum Across Multiple Tabs?

Thanks Guys . . . BlueHornet, I thought about doing it that way, but I wanted to avoid having to manually sum cells in 50 worksheets . . . also, if I change the order of one then I will have to redo the entire formula.

I tried downloading those two programs but I think the firewall here at work is preventing me from downloading them. But lets say I have a simple worksheet where

Sheet1 I have a table like this:

Manager A \$900
Manager B \$600
Manager C \$500

In Sheet 2 I have another table like this:

Manager C \$200
Manager D \$500
Manager A \$100

In Sheet 3 I have:

Manager C \$200
Manager A \$5
Manager D \$70

Can I now create a summary sheet that will do a vlookup to each of the managers in each sheet and sum their total amounts?

7. ## Re: Vlookup and Sum Across Multiple Tabs?

Originally Posted by Dean99
Thanks Guys . . . BlueHornet, I thought about doing it that way, but I wanted to avoid having to manually sum cells in 50 worksheets . . . also, if I change the order of one then I will have to redo the entire formula.

I tried downloading those two programs but I think the firewall here at work is preventing me from downloading them. But lets say I have a simple worksheet where

Sheet1 I have a table like this:

Manager A \$900
Manager B \$600
Manager C \$500

In Sheet 2 I have another table like this:

Manager C \$200
Manager D \$500
Manager A \$100

In Sheet 3 I have:

Manager C \$200
Manager A \$5
Manager D \$70

Can I now create a summary sheet that will do a vlookup to each of the managers in each sheet and sum their total amounts?
I have created a sheet with 4 tabs. first Tab is "Summary" and the other three are "sheet1""sheet2""sheet3" In these sheets i have Manager in column A and \$ in Column B. The Summary sheet is exactly the same and i have entered the following formula to calculate the managers totals over the 3 sheets.

=SUMIF(Sheet1!A:A,Summary!A1,Sheet1!B:B)+SUMIF(Sheet2!A:A,Summary!A1,Sheet2!B:B)+SUMIF(Sheet3!A:A,Summary!A1,Sheet3!B:B)

Thanks

Jamie

8. ## Re: Vlookup and Sum Across Multiple Tabs?

Originally Posted by jambo72uk
I have created a sheet with 4 tabs. first Tab is "Summary" and the other three are "sheet1""sheet2""sheet3" In these sheets i have Manager in column A and \$ in Column B. The Summary sheet is exactly the same and i have entered the following formula to calculate the managers totals over the 3 sheets.

=SUMIF(Sheet1!A:A,Summary!A1,Sheet1!B:B)+SUMIF(Sheet2!A:A,Summary!A1,Sheet2!B:B)+SUMIF(Sheet3!A:A,Summary!A1,Sheet3!B:B)

Thanks

Jamie
Issue you will have though is you wont be able to input 50 seperate arguments as excel wont allow. Is there no way you can put the date in one tab and use a pivot to calculate it?

Thanks,

Jamie

9. ## Re: Vlookup and Sum Across Multiple Tabs?

If you had faloow the links I have posted you woudl get this
Excel 2010
AB
1ManagerSales
2Manager A\$900
3Manager B\$600
4Manager C\$500
Sheet1

Excel 2010
AB
1ManagerSales
2Manager C\$200
3Manager D\$500
4Manager A\$100
Sheet2

Excel 2010
AB
1ManagerSales
2Manager C\$200
3Manager A\$5
4Manager D\$70
Sheet3
Excel 2010
ABCDEF
1ManagerSales
2Manager A1005Sheet1
3Manager B600Sheet2
4Manager C900Sheet3
5Manager D570

Summary

Worksheet Formulas
CellFormula
B2=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A2:A100"),A2,INDIRECT("'"&list&"'!B2:b100 ")))

Workbook Defined Names
NameRefers To
list=Summary!\$F\$2:\$F\$4

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•