Subtotal formula query

[cuddy89]

Please can I have help combining the SUBTOTAL(9,...) formula with the formula below, so that the figures update when a filter is applied to the data

=(SUMPRODUCT(RH4:RH530,INDEX(L4:RD530,,MATCH(B1,L2:RD2)))/SUM(INDEX(L4:RD530,,MATCH(B1,L2:RD2))))/1.2

Thank you

Richard

 

[Fluff]

One option is to use a helper column with

=SUBTOTAL(103,A4)

Change col A to a column that will always have a value. And then use

=(SUMPRODUCT(RH4:RH530,INDEX(L4:RD530,,MATCH(B1,L2:RD2)),--(RI4:RI530=1))/SUBTOTAL(109,INDEX(L4:RD530,,MATCH(B1,L2:RD2))))/1.2

Change the RI column to reference the helper column.

 

[cuddy89]

Thank you, much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.