Dynamic sum of filter formula

[GraemeSS]

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.

[#2]

@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))

[#3]

@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.

[#4]

@GraemeSS 

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

[#6]

@Riny_van_Eekelen Thank you! Yes I agree, my boss is pretty set in her ways but this really makes sense. I will try to work it this way. I have also asked for my work to allow me to have access to the VSTACK and HSTACK functions.

[#7]

@Riny_van_Eekelen Thank you again. I intended to mark your response as the accepted solution but being a newb I accidentally selected my reply. I can't see a way of undoing it but if someone can tell me I will change it.
Thank you to @Harun24HR as well. Apologies for not articulating the problem more clearly.

[#8]

@GraemeSS I could "unmark" your answer. Try again :)

[#9]

Done! Thanks, it did not come up as an option until I went back into the thread from the beginning. :)