Hello! I'm trying to create a formula that adds up all the quantities of a specific type across multiple sheets (but not all sheets). Below is a summary of the excel file and what I am trying to achieve. I have the formula created, there is just one aspect that I want it to do that I can't figure out. I have attached a simplified sample file to this thread.
Sheet 1: Total Quant by Type
- The formula I am trying to write is in cells B2 through B7. (highlighted in yellow)
- This formula totals the quantities of each Type (column A) in the sheets that are listed out in the "Sheets Included" sheet.
Sheet 2: Sheets Included
- This lists out the sheets that I would like the quantity formula in Sheet 1 to include.
- Here is what I can't figure out. In the future, more sheets will be added which need to be included in the Sheet 1 quantity formula. I want the quantity formula in Sheet 1 to automatically update when new sheets are added to this list. Is there an update I can make to the formula that tells it to include all cells, starting at A2 going down until the next cell is blank?
Here is my current formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&'Sheets Included'!A$2:A$4&"'!A$3:A$1000"),'Total Quant by Type'!A2,INDIRECT("'"&'Sheets Included'!A$2:A$4&"'!B$3:B$1000")))
Essentially, I want to update the bolded portion of the formula so that it updates automatically when new sheets are added under the "Sheets Included" list. Anyone have any ideas on how to do this?
Thanks!
Bookmarks