Countif with filters, using sumproduct, subtotal and indirect

Hi All

Can anyone workout what is wrong with this formula?

its a countif style formula to work with filtered data, but i cannot get it work (accept it)

=sumproduct(subtotal(3,offset(INDIRECT("'"&$C$2&"'!$B$14:$B$46",ROW(indirect("'"&$C$2&"'!$B$14:$B$46")-MIN(Row(indirect("'"&$C$2&"'!$B$14:$B$46")),,1))*(indirect("'"&$C$2&"'!$B$14:$B$46="Active"))

I based the above formula on this one =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality")) but added the indirect elemets to make it work across multiple sheets using a drop down.

Thanks

In advance

Stephen

OFFSET and INDIRECT don't work very well together, as they are both volatile functions, but you are missing a close bracket at the end of the first INDIRECT term.

Hope this helps.

Pete

This final part looks wrong

Formula:

Please Login or Register  to view this content.