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

[littlevillage]

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

[#2 · Accepted 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.

[#3]

@Harun24HR 

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.