Forum Discussion
Solved
Formula that sums inventory items from multiple sheets using 2 criteria
Hello!
I'm using a workbook to keep track of a large collection of LEGO parts. The first sheet is master list of every part, sorted by part #, color, and name. Column E is the quantity of parts that are loose in storage. I want Column F to show me the quantity of parts that are bundled together in sets. The other sheets in the workbook are inventories of the parts in these sets (also sorted by #, color, and name). What I would like is a formula in Column F on the master list that will scan the other sheets, find part quantities based on Part# and Color, and give me the total in the master list. I had some success using SUMIFS and INDIRECT functions to do this with 2 of the sheets, but it failed to work once I started including 3 or more sheets.
This is the formula that worked when only referencing 1 sheet, but not more. "SheetList" is the name assigned to the list of sheet names I created in another tab.
=SUM(SUMIFS(INDIRECT("'"&SheetList&"'!"&"E2:E200"),INDIRECT("'"&SheetList&"'!"&"C2:C200"),C137,INDIRECT("'"&SheetList&"'!"&"F2:F200"),H137))
I'm on a Windows 10 laptop using Excel (Version 2310 Build 16.0.16924.20054). Thanks!
Here's a general formula you can try to use:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!E2:E200"),INDIRECT("'"&SheetList&"'!C2:C200"),C137,INDIRECT("'"&SheetList&"'!F2:F200"),H137))
Formula is untested.
Using INDIRECT with a range of cells from multiple sheets can be resource intensive and may slow down performance, especially as your data grows. If you experience performance issues, consider alternative approaches, such as using Power Query or consolidating your data into a single table.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
I would avoid INDIRECT if any other solutions are available.
Have you tried appending the data using PowerQuery and then grouping by Part ID?
Another option using 365 is to identify the data as a named 3D range and use VSTACK to convert it to a single array. I think you would then need to filter the quantities by each Part ID in turn using MAP and SUM. There is a new function GROUPBY which is in the process of being rolled out as we speak that should perform the last task more efficiently.
Here's a general formula you can try to use:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!E2:E200"),INDIRECT("'"&SheetList&"'!C2:C200"),C137,INDIRECT("'"&SheetList&"'!F2:F200"),H137))
Formula is untested.
Using INDIRECT with a range of cells from multiple sheets can be resource intensive and may slow down performance, especially as your data grows. If you experience performance issues, consider alternative approaches, such as using Power Query or consolidating your data into a single table.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Hello,
When I tried this formula, I got the same #REF error I did with mine. The problem seems to be with the formula trying to reference more than one sheet, as the formula works fine when I edit the SheetList to just include one of the sheet names. I used the Evaluate Formula tool and these were the first steps that led to the #REF error. I can try to upload my workbook to OneDrive if that's helpful.
- If you're encountering a #REF error, there might be some issue with the references or sheet names. Uploading the workbook to OneDrive would be helpful, and I can take a closer look at the structure and content of the workbook to provide a more accurate solution.
