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 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.
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.
8 Replies
- Harun24HR thank you so much for your reply. Unfortunately I didn't give enough context. Each month I have to produce a statement of recharges back to different parts of a organisation. I have to provide each section of the organisation with their own report which is a subset of the main report.
Currently I have to manually sort and filter the summary page for each section and then remove all the other sections charges from the detailed tab.
I am trying to create the 12 section reports using filters from the main report all in one 'working' folder.
I then hope to name the overall monthly report the same as the main report in the 'working' folder and overwrite the main report.
If everything works then I will save the values of the 12 section reports and distribute them.
(Sorry to be so long winded).
For this report to match the formatting that my boss wanted I have to have a total at the end of column C that moves up and down according to the last populated cell in column C. I am not sure if this can be done.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))
