Is there a new function to replace SUMPRODUCT

[Bob_Collins]

The situation in general

• I have 35 identical Sheets in one file that are tabular for product name, list and discounted price.
• Each sheet includes a cell (R166) that identifies the agreement number
• There can be multiple agreement numbers in one file and several sheets can use the same agreement number
• I need to sum up the line pricing in a rollup sheet based on the agreement number to fill out the rollup table for that account number
• I have a rollup or summary sheet that duplicates the 35 sheet’s tables.
• So, if 5 sheets have the same agreement number then a cell (say G9 ) of each of those sheets is summed with SUMPRODUCT
• The rollup can handle up to 15 summation tables based on the Account number

I was hoping that someone could point me at a new function that could simplify this effort.  The rollup has many formulas and I would like to make this easier and more productive

Here is the basic formula that is in each cell of each rollup table:

=IF(B2=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!$R$166"),B2,INDIRECT("'"&TabNames&"'!G9"))))

Breakdown

• B2, B150, B298… is a cell in the each of the summary tables that pulls in the account number to use for that summary table and if it is not populated I stop there to improve speed  -  and is not really part of this question

• Tabnames is a table(range name) of Sheet Names used for each of the 35 pricing sheets.  I then indirectly pull R166 value from each sheet in Tabnames range and if it matches the B2, Bxx,… cell for that summary table I sum up a Cell (G9 in this case) for each sheet that has the Account number referenced in B2.

I have 1 of these formulas in each cell of each summary table so it is cumbersome and I would like to learn a bit and use a new  function to do this easier if possible. It does all work but I was hoping that I could streamline it with a suggestion of a formula that could replace it.

Hopefully, one of the new lookup formulas could do the same function with multiple criteria or some type of Array or table summation since the tables are identical.

The 35 sheets internal (VBA) names are sequential, Sheet1 – Sheet35,  so I can do some “ for-next” loops in VBA if it helps. Just looking for a slick way to improve this.

[#2]

@Bob_Collins 

You could use 3D-referencing. See here: Help

The problem is that not all formulas work with 3D. SUM works, but unfortunately SUMIF does not. Therefore, I had to reach into my bag of tricks. In the new function VSTACK (currently only available in Microsoft 365 Insider or Excel for the Web) you can use 3D referencing.

=SUM(IF(VSTACK(Sheet1:Sheet35!R166)=B2,VSTACK(Sheet1:Sheet35!G9),0))

If you are not an insider, you can test the whole thing in Excel for the Web.

Good luck!

[#3]

Thanks for the info. I am only using 365 Enterprise and any solution I work has to be sourced from there. I am planning on trying Power Query to see if I can do this with some helper tables. What I have does work it is just a huge load on the file with over 80,000 formulas. I was hoping to find some 3D array functions to sum and filter the tables. Since they are all identical table/forms it would have been great to be able to sum entire table cells with a criteria. I will keep working on it. Might have to move it to VBA to clean it up. It keeps growing each year I use it so this was always going to be an issue at some point. Thanks again for your reply.