Aug 24 2022 05:25 PM
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 I would like to do is have a sum of all the values displayed in column C at the end. It needs to move up or down to adjust to the last number in the column.
Aug 24 2022 07:27 PM
@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))
Aug 24 2022 09:37 PM
Aug 24 2022 10:43 PM
Solution@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.
Aug 25 2022 12:33 AM
Aug 29 2022 10:43 PM
Aug 29 2022 10:52 PM
@GraemeSS I could "unmark" your answer. Try again :)
Aug 29 2022 10:56 PM
Aug 24 2022 10:43 PM
Solution@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.