Forum Discussion

AWDarby's avatar
AWDarby
Copper Contributor
Nov 27, 2023
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 t...
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Nov 28, 2023

    AWDarby 

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 28, 2023

AWDarby 

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.

Resources