Summarise data and group together

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



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.


File attached.



As variant, if like this


 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?


Got it, will update bit later today.

best response confirmed by aavgoustinoss (Occasional Contributor)


It's like



 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