Aug 21 2022 09:15 PM
Hi,
I have created an array and sorted it
I try to put a row Grand Total at the end of the array
with expect when adjust datasets the array will adjust corresponding
Thank you
Aug 21 2022 09:43 PM - edited Aug 21 2022 09:58 PM
Solution@littlevillage If you have access to VSTACK() formula then could try below.
=LET(lmd,LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]<>0)),a,SUMIF(Table1[QLV],x,Table1[BILL TRỪ HQ]),b,SUMIF(Table1[QLV],x,Table1[CƯỚC TRỪ HQ]),c,b/a,SORT(CHOOSE({1,2,3,4},x,a,b,c),2,-1)),VSTACK(lmd,BYCOL(lmd,LAMBDA(col,IF(SUM(col)=0,"Total",SUM(col))))))
If you do not have access to VSTACK then can try FILTERXML(). So, calculation part of LET() function will be-
INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&"</s></t>","//s"),SEQUENCE(ROWS(x)+1,4))
And final formula would be-
=LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]<>0)),a,SUMIF(Table1[QLV],x,Table1[BILL TRỪ HQ]),b,SUMIF(Table1[QLV],x,Table1[CƯỚC TRỪ HQ]),c,b/a,p,SORT(CHOOSE({1,2,3,4},x,a,b,c),2,-1),INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&"</s></t>","//s"),SEQUENCE(ROWS(x)+1,4)))
You will find both solution to attached file.
Aug 25 2022 05:02 PM - edited Aug 25 2022 05:07 PM
Thank you Harun,
It is worked,
I just highlight color for last row in the array and when data adjust, the values are automatically updated but the highlight color is not.
Aug 21 2022 09:43 PM - edited Aug 21 2022 09:58 PM
Solution@littlevillage If you have access to VSTACK() formula then could try below.
=LET(lmd,LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]<>0)),a,SUMIF(Table1[QLV],x,Table1[BILL TRỪ HQ]),b,SUMIF(Table1[QLV],x,Table1[CƯỚC TRỪ HQ]),c,b/a,SORT(CHOOSE({1,2,3,4},x,a,b,c),2,-1)),VSTACK(lmd,BYCOL(lmd,LAMBDA(col,IF(SUM(col)=0,"Total",SUM(col))))))
If you do not have access to VSTACK then can try FILTERXML(). So, calculation part of LET() function will be-
INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&"</s></t>","//s"),SEQUENCE(ROWS(x)+1,4))
And final formula would be-
=LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]<>0)),a,SUMIF(Table1[QLV],x,Table1[BILL TRỪ HQ]),b,SUMIF(Table1[QLV],x,Table1[CƯỚC TRỪ HQ]),c,b/a,p,SORT(CHOOSE({1,2,3,4},x,a,b,c),2,-1),INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&"</s></t>","//s"),SEQUENCE(ROWS(x)+1,4)))
You will find both solution to attached file.