Jan 18 2022 02:23 AM - edited Jan 18 2022 02:24 AM
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.
Jan 18 2022 02:29 AM
Jan 18 2022 02:56 AM
@aavgoustinoss You can use COUNTIF for this, and since you use MS365, you can make use of spilling dynamic array functions.
File attached.
Jan 18 2022 03:26 AM
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) )
Jan 18 2022 03:56 AM
Jan 18 2022 05:12 AM
@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?
Jan 18 2022 05:40 AM
Got it, will update bit later today.
Jan 18 2022 06:54 AM
SolutionIt'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) )
Jan 18 2022 06:54 AM
SolutionIt'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) )