Applying a filter to a SUMIFS range

[Jockeyp]

Hello,

I currently have two tabs in a spreadsheet. One is a database and the other is sheet is called 'Builder'. This contains a series of tables that use SUMIFS to apply multiple criteria to the database to get a result. What I want to do is to adjust my formulas so that when i apply a filter to the database, this is automatically reflected in my SUMIFS formulas on the Builder tab. Ideally I would like to add multiple filters to the database and have those reflected in the sum numbers on the builder tab.

Do i need to create an Array formula for this to work? I have attached a picture of the Builder tab and the formula that I have built in the various cells in the tables in the Builder tab is provided below. You will see that the formula is already a bit complicated as I am using INDIRECT in the SUMIFS formulas. Hopefully the picture quality is good enough for people to view it.

=SUMIFS(INDIRECT(E$21),INDIRECT($M$12),$E$15,INDIRECT($J$3),">="&$A22,INDIRECT($J$3),"<="&$B22)

Appreciate any help with this.

Thanks

Neil

 

[Jockeyp]

From reading other threads I have managed to solve this. I added a 'Helper' column to my database that used the subtotal formula with the COUNTA function and the 103 version selected to not include hidden cells. I then created a reference cell on my Builder sheet for this column. Finally, I added an additional argument into my SUMIFS formula using Indirect to target the Helper column.