Forum Discussion
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...
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) )
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?
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) )- thank you works perfectly!
aavgoustinoss , you are welcome