Using SUMIFS on filtered cells

[N HANCOCK]

Hi, I need the following formula to calculate only the visible cells when the table from which the data has come is filtered:

=IFERROR(
(SUMIFS(
Table2[Hourly rates (inc. salaried)],
Table2[Gender], L2,
Table2[Hourly paid or salaried?], K2
) + SUMIFS(
Table2[Hourly rates (inc. salaried)],
Table2[Gender], L2,
Table2[Hourly paid or salaried?], K3
))/('Data sheet (hidden)'!D14+'Data sheet (hidden)'!G14),"-")

Anhy ideas? I am stumped!

 

[Domenic]

Try the following formula...

=IFERROR(SUMPRODUCT(--(Table2[Gender]=L2),--(ISNUMBER(MATCH(Table2[Hourly paid or salaried?],K2:K3,0))),SUBTOTAL(9,OFFSET(Table2[Hourly rates (inc. salaried)],ROW(Table2[Hourly rates (inc. salaried)])-MIN(ROW(Table2[Hourly rates (inc. salaried)])),0,1)))/('Data sheet (hidden)'!D14+'Data sheet (hidden)'!G14),"-")

Hope this helps!