I've created the following sample tables in the attached file with the sole purpose to keep this question as straightforward as humanly possible...
As you can see the upper table brings the original set of data for each month with total bottom rows across the columns and annualized totals for each product, as well as an annualized grand total at its lower right corner.
And the lower table sums up bi-monthly totals for each pair of adjacent months with a total bottom row across the columns.
Question Tables.png
As you can see on the screen shot that follows, I managed to apply spilled range formulas for everything I could, except for the bi-monthly totals for each pair of adjacent months on the lower table.
Question Tables Issue.png
Question: How can I replace that conventional "=SUM(C6:D6)" highlighted in red that I have to copy and paste down and across the table with a 2D one-cell spilled range formula that does the same job, which would allow me to fill down and across the entire lower table (except the "Total" row, which is already using a spill range formula), just like the other formulas highlighted in blue?
It is imperative though to get to a formula solution, which means no VBA gimmicks, pivot table, helper columns/rows/sheets to make this come to fruition...
I tried the same MMULT approach with nested OFFSET/TRANSPOSE/SEQUENCE functions, but it didn't work.
I also scoured Google looking for an answer to no avail, so this is my last resort - please help! I can’t believe Excel cannot come to such a solution!!
P.S.: And before someone asks me why would I want to complicate things for such a basic worksheet: as I said, the above is just a simplified sample to make it easier to explain what I want to achieve, as the schedule I want to apply this solution to is extremely complex, so I've been looking for ways to make it more nimble and thereby improve its processing performance which is currently pretty slow.
Bookmarks