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

Copper Contributor

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...

Dazzathedrummer_1-1719414809089.png

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...

Dazzathedrummer_2-1719415042871.png

Dazzathedrummer_3-1719415145287.png

(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?

 

 

2 Replies

Hi @Dazzathedrummer 

 

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

Sample.png

 

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)), "")
)
Thanks - I'll check that out!