Forum Discussion

aavgoustinoss's avatar
aavgoustinoss
Copper Contributor
Jan 18, 2022
Solved

Summarise data and group together

Hello! i was wondering if something like this is possible. we have a food menu for ordering. when the orders are placed on the top tables, i want then a formula or function to summarize the orders an...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 18, 2022

    aavgoustinoss 

    It's like

    with

    =LET(
 main,        LET(t, INDEX(dataMonday,0,1), FILTER(t,  t<>"") ),
 Side1,       LET(t, INDEX(dataMonday,0,2), FILTER(t,  t<>"") ),
 Side2,       LET(t, INDEX(dataMonday,0,3), FILTER(t,  t<>"") ),
 nA,          ROWS(main),
 nB,          ROWS(Side1),
 nC,          ROWS(Side2),
 n,           nA + nB + nC,
 j,           SEQUENCE(n),
 fullList,    IF( j <= nA, INDEX(main, j),
              IF( j<= nA+nB, INDEX(Side1, j-nA),
                  INDEX( Side2, j-nA-nB) ) ),
 names,       UNIQUE(fullList),
 countNames,  MMULT(--(names=TRANSPOSE(fullList)), SEQUENCE(n,,1,0)),
 IF({1,0}, names, countNames) )
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 18, 2022

aavgoustinoss  You can use COUNTIF for this, and since you use MS365, you can make use of spilling dynamic array functions.

File attached.

 

  • aavgoustinoss's avatar
    aavgoustinoss
    Copper Contributor
    Jan 18, 2022
    thank you, this has helped me a lot!

Resources