Forum Discussion
Is there a new function to replace SUMPRODUCT
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 num...
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!
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.