Welcome to the board!
A formula like this would work:
As long as the total is in the same cell on every sheetCode:=SUM(Sheet2!A1,Sheet4!A1)
Michael
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 ...
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!
Welcome to the board!
A formula like this would work:
As long as the total is in the same cell on every sheetCode:=SUM(Sheet2!A1,Sheet4!A1)
Michael
"It is so nice, to see so many, that know so much"
Using Excel 2007
If data in your sheets have the same layout you may look at this topics:
http://www.mrexcel.com/forum/excel-questions/602372-summing-specified-value-across-multiple-worksheets-vlookup-array-sumproduct-sumif-post2983726.html
http://www.mrexcel.com/forum/excel-questions/623976-sumif-vlookup-help-post3095627.html
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
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!
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?
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.
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
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
If you had faloow the links I have posted you woudl get this
Excel 2010
A B 1 Manager Sales 2 Manager A $900 3 Manager B $600 4 Manager C $500 Sheet1
Excel 2010
A B 1 Manager Sales 2 Manager C $200 3 Manager D $500 4 Manager A $100 Sheet2
Excel 2010
A B 1 Manager Sales 2 Manager C $200 3 Manager A $5 4 Manager D $70 Sheet3Excel 2010
A B C D E F 1 Manager Sales 2 Manager A 1005 Sheet1 3 Manager B 600 Sheet2 4 Manager C 900 Sheet3 5 Manager D 570 Summary
Worksheet Formulas
Cell Formula B2 =SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A2:A100"),A2,INDIRECT("'"&list&"'!B2:b100 ")))
Workbook Defined Names
Name Refers To list =Summary!$F$2:$F$4
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Like this thread? Share it with others