Forum Discussion
Solved
Dynamic sum of filter formula
Hi team, I have been given a spreadsheet by my boss that I am trying to add some automation to. I have added a filter formula that changes according to the criteria I put in a specified cell. What...
GraemeSS You are making it more complicated if you insist on showing the total at the bottom, if you do not have the VSTACK and HSTACK functions that Harun24HR used. If you can convince your boss to move the total to the top of the report (which would be my preferred lay-out), everything will be done very easily. I created a working example in your file. Select the cluster you want the report for from the dropdown in A1 and all will update dynamically.
GraemeSS If you have access to VSTACK() formula then use.
=VSTACK(FILTER(A:B,A:A=E1),HSTACK("Total",SUM(FILTER(B:B,A:A=E1))))Else use below formula
=INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,FILTER(A:B,A:A=E1),"Total",SUM(FILTER(B:B,A:A=E1)))&"</s></t>","//s"),SEQUENCE(COUNTA(FILTER(B:B,A:A=E1))+1,2))