Hi, I am fairly new to this, Can you please let me know if there is a way to get a sum of ex: A2 if it is produced in any of my tabs at the bottom? There is a chance that any of my items can be produced many of times thru out the year and I would like to keep a track of how much we produce of each Product No. On the TEST SHEET tab is where I would like to get the information from every other tabs or sheets not sure what to call them. I would like to bring back the totals from column L, M, N, O and P if possible please. I have attached an example of my excel sheet, this will be updated each day with last nights production. Please and Thank you!
[Solved] Sum of Items on different Sheets?
- Thread starter Ti-Paul
- Start date
Hello Ti-Paul,
You can total a specific Product No across multiple sheets using this formula; no VBA needed.
First, list all the sheet names (except the TEST SHEET) in a range, say X2:X20, and name that range Tabs.
Then use this formula in TEST SHEET!L2 (assuming the Product No is in A2):
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),$A2,INDIRECT("'"&Tabs&"'!L:L")))
This adds all values from column L across every sheet listed in Tabs where the Product No matches A2.
Just copy the same formula for M, N, O, and P columns by changing the last column letter.
It’s an easy and dynamic way to sum matching items from multiple sheets.
You can total a specific Product No across multiple sheets using this formula; no VBA needed.
First, list all the sheet names (except the TEST SHEET) in a range, say X2:X20, and name that range Tabs.
Then use this formula in TEST SHEET!L2 (assuming the Product No is in A2):
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),$A2,INDIRECT("'"&Tabs&"'!L:L")))
This adds all values from column L across every sheet listed in Tabs where the Product No matches A2.
Just copy the same formula for M, N, O, and P columns by changing the last column letter.
It’s an easy and dynamic way to sum matching items from multiple sheets.
Hello Ti-Paul,
You’re very welcome, and great question! Yes, the formula looks for the Product No that’s in the cell you reference (in this case, $A2).
So, if your Product No is in a different cell, for example, B2 or D5, you just need to change $A2 in the formula to that cell reference.
For example, if your Product No is in B2, the formula becomes:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),$B2,INDIRECT("'"&Tabs&"'!L:L")))
As long as you update that reference, it will correctly find and sum the matching Product No across all the sheets listed in your Tabs range.
You’re very welcome, and great question! Yes, the formula looks for the Product No that’s in the cell you reference (in this case, $A2).
So, if your Product No is in a different cell, for example, B2 or D5, you just need to change $A2 in the formula to that cell reference.
For example, if your Product No is in B2, the formula becomes:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),$B2,INDIRECT("'"&Tabs&"'!L:L")))
As long as you update that reference, it will correctly find and sum the matching Product No across all the sheets listed in your Tabs range.