Forum Discussion
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 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.
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) )
10 Replies
aavgoustinoss You can use COUNTIF for this, and since you use MS365, you can make use of spilling dynamic array functions.
File attached.
- thank you, this has helped me a lot!
- 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.
As variant, if like this
=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) )SergeiBaklan 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?
