I would like to get a total line at the end of my dynamic filter formula in excel. The problem is how do I shift the total line when the array grows or shrinks? I always want total at the end of the filtered data.
3 Answers
Bit of a stretch, but if you want to do this using excel-formulae then you could try the below:
Formula in E5:
=IFERROR(INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",0,FILTER(A5:C14,C5:C14>15000),"","Total",SUMIF(C5:C14,">15000"))&"</s></t>","//s"),SEQUENCE(COUNTIFS(C5:C14,">15000")+1,3)),"")
I'd advise to keep the 15000 as a reference instead of hardcoded obviously.
Also a bit of a stretch using the idea of indexing from @JvdV:
=LET(nrow,COUNTIF(C5:C14,">"&15000),seq,SEQUENCE(nrow+1,3,0),IFS(seq=nrow*3,"",seq=nrow*3+1,"Total",seq=nrow*3+2,SUM(FILTER(C5:C14,C5:C14>15000)),TRUE,INDEX(FILTER(A5:C14,C5:C14>15000),QUOTIENT(seq,3)+1,MOD(seq,3)+1)))
You could if you wished apply bold font using conditional formatting to the output range using the formula
=$F5="Total"
This one is a little shorter.
=LET(f,FILTER(A5:C14,C5:C14>15000),size,ROWS(f),IF(SEQUENCE(size+1)>size,CHOOSE({1,2,3},"","Total",SUM(INDEX(f,,3))),f))
Related