Forum Discussion

Dazzathedrummer's avatar
Dazzathedrummer
Copper Contributor
Jun 26, 2024

Trying to create a 'one cell' table using dynamic arrays...

Hi,

I'm trying to create a summary table using dynamic arrays for a budget document - the idea is that the table picks up any changes in data that the budget holder makes .

 

I started out by just spilling out categories per type and having SUMIFS() in each month column but I'd really like to be able to hit this in one.

 

This is the table which contains the start point of the data...

The budget holder types in the numbers and can add rows for new suppliers etc as needed.

I've created the following using VSTACK() to present the left column as I need it...

(I'll format the headings using Conditional Formatting).

 

I'm having trouble figuring out how to populate the Month columns - either within the same formula or in separate formulas in adjacent columns. I've tried adding HSTACK() to Filter() lines to put a SUMIFS() after the FILTER() but I just get some crazy random results.

 

Can this be done like this?

 

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 03, 2024

    Hi Dazzathedrummer 

     

    Not sure this is what you expect (if not provide a picture of the expected result):

     

    With data formatted as table named Table1:

    =LET(
  StackCat, LAMBDA(init,type,
      VSTACK(init, type, FILTER(Table1, Table1[Type] = type), "")
  ),
  array,    DROP( DROP( REDUCE(0,UNIQUE(Table1[Type]), StackCat), 1), -1),
  kcols,    SEQUENCE(,COLUMNS(array)),
  IFNA( FILTER(array, IF(kcols = 2, 0, kcols)), "")
)

Resources