+ Reply to Thread
Results 1 to 3 of 3

2D Spilled Range Sum Formula

  1. #1
    Registered User
    Join Date
    11-27-2015
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    MS Office 365
    Posts
    42

    Question 2D Spilled Range Sum Formula

    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.

  2. #2
    Registered User
    Join Date
    11-27-2015
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    MS Office 365
    Posts
    42

    Re: 2D Spilled Range Sum Formula

    Just found out the solution via another forum:

    =C6:M8+D6:N8

    Just found out the reason I couldn't find a single thread around the web with such question: the solution is so darn simple hahaha!!!

  3. #3
    Registered User
    Join Date
    11-27-2015
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    MS Office 365
    Posts
    42

    Re: 2D Spilled Range Sum Formula

    My apologies for posting the same question on a separate forum as I spent so much time looking for an answer and was stuck trying to improve my file.

    I tried to post the link related to the aforementioned post as instructed, but wasn't allowed to.

    It was on the Microsoft Community forum...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Listboxes and dynamic/spilled arrays
    By Mal Ba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2021, 09:06 PM
  2. Formula - Sum and multiplying - Dynamic array spilled behavior
    By ttch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-30-2020, 02:54 PM
  3. Spilled Data With Two Criterias
    By MattheusB in forum Excel General
    Replies: 1
    Last Post: 11-05-2020, 02:12 PM
  4. Concat spilled data
    By aguanigei in forum Excel General
    Replies: 5
    Last Post: 04-20-2020, 10:48 AM
  5. Countifs / Sumifs with dynamic arrays (spilled ranges)
    By esbencito in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-08-2020, 02:43 PM
  6. [SOLVED] YTD with dynamic spilled array
    By MatthewHart74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2019, 07:49 AM
  7. YTD with spilled dynamic array
    By MatthewHart74 in forum Office 365
    Replies: 0
    Last Post: 12-12-2019, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1