SOLVED

Summarise data and group together

Copper Contributor

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 and create totals for each item. an example of what i want is on the screenshot below, i've filled in the first table (monday) to better understand what the bottom table(monday) should look like. unfortnetely i cannot use macros for this, so i need to have a formula. i'm using Microsoft Office 365 for business. 

2022-01-18_12h22_10.png

 

10 Replies
please note that menu items are not fixed. many other options can be added. for example if one user adds pizza on the mains, then an automatic cell on the bottom table should be created and next to it the total, in this case 1.

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

Riny_van_Eekelen_0-1642503316697.png

File attached.

 

@aavgoustinoss 

As variant, if like this

image.png

=LET(
 n,          ROWS(dataMonday),
 main,       INDEX(dataMonday,0,1),
 Side1,      INDEX(dataMonday,0,2),
 Side2,      INDEX(dataMonday,0,3),
 nameMain,   UNIQUE(main),
 countMain,  MMULT(--(nameMain=TRANSPOSE(main)), SEQUENCE(n,,1,0)),
 nameSide1,  UNIQUE(Side1),
 countSide1, MMULT(--(nameSide1=TRANSPOSE(Side1)), SEQUENCE(n,,1,0)),
 nameSide2,  UNIQUE(Side2),
 countSide2, MMULT(--(nameSide2=TRANSPOSE(Side2)), SEQUENCE(n,,1,0)),
 mMain,      ROWS(nameMain),
 mSide1,     ROWS(nameSide1),
 mSide2,     ROWS(nameSide2),
 k,          SEQUENCE(mMain+mSide1+mSide2),
 names,      IF( k <= mMain, INDEX(nameMain, k),
             IF( k<= mMain+mSide1, INDEX(nameSide1, k-mMain),
                 INDEX(nameSide2, k-mMain-mSide1))),
 counts,      IF( k <= mMain, INDEX(countMain, k),
              IF( k<= mMain+mSide1, INDEX(countSide1, k-mMain),
                  INDEX(countSide2, k-mMain-mSide1))),
 IF({1,0}, names, counts)  )

 

thank you, this has helped me a lot!
thank you @Sergei! thats a great formula you've added!

@Sergei Baklan the solution seems to work, but it also counts cells with no value. is there a way to skip it when the value is an empty cell?

@aavgoustinoss 

Got it, will update bit later today.

best response confirmed by aavgoustinoss (Copper Contributor)
Solution

@aavgoustinoss 

It's like

image.png

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) )
thank you works perfectly!

@aavgoustinoss , you are welcome

1 best response

Accepted Solutions
best response confirmed by aavgoustinoss (Copper Contributor)
Solution

@aavgoustinoss 

It's like

image.png

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

View solution in original post