How can to put a row Grand Total at the end of a sorted array.

Question

Hi,I have created an array and sorted itI try to put a row Grand Total at the end of the arraywith expect when adjust datasets the array will adjust correspondingThank you&nbsp;

harun24hr · Accepted Answer

littlevillage&nbsp;If you have access to VSTACK() formula then could try below.&nbsp;=LET(lmd,LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]&lt;&gt;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))))))&nbsp;If you do not have access to VSTACK then can try FILTERXML(). So, calculation part of LET() function will be-INDEX(FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;TEXTJOIN("&lt;/s&gt;&lt;s&gt;",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&amp;"&lt;/s&gt;&lt;/t&gt;","//s"),SEQUENCE(ROWS(x)+1,4))&nbsp;And final formula would be-=LET(x,UNIQUE(FILTER(Table1[QLV],Table1[QLV]&lt;&gt;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("&lt;t&gt;&lt;s&gt;"&amp;TEXTJOIN("&lt;/s&gt;&lt;s&gt;",FALSE,p,"Total",SUM(a),SUM(b),SUM(c))&amp;"&lt;/s&gt;&lt;/t&gt;","//s"),SEQUENCE(ROWS(x)+1,4)))You will find both solution&nbsp; to attached file.

Forum Discussion

How can to put a row Grand Total at the end of a sorted array.

2 Replies

Resources