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.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
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.

Resources