Forum Discussion
Formula that sums inventory items from multiple sheets using 2 criteria
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.
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.
- NikolinoDE
For some reason, your formula is working perfectly now. I tinkered around for awhile. Not sure what or if I changed anything, but it's now working across 6 different sheets. Thanks!
I may try to do the PowerQuery route later on if the workbook becomes to unwieldy with additional sheets.
