COUNTIF with SUBTOTAL?

[MoreGone]

I have a bunch of TEXT statuses that I am taking count of. The Statuses are in K, Count of all in L and would like to count filtered in M.

In the L column I have a working

=COUNTIF($L$11:$L$2000,K2)
=COUNTIF($L$11:$L$2000,K3)

etc.

But I am now trying to do a COUNTIF with SUBTOTAL which is NOT a function of SUBTOTAL, how do I go about this?

 

[Eric W]

Try:

=SUM(IF(SUBTOTAL(103,OFFSET($L$11,ROW($L$11:$L$2000)-ROW($L$11),0)),IF($L$11:$L$2000=K2,1)))

confirmed with Control+Shift+Enter.

 

[MoreGone]

Try:

=SUM(IF(SUBTOTAL(103,OFFSET($L$11,ROW($L$11:$L$2000)-ROW($L$11),0)),IF($L$11:$L$2000=K2,1)))

confirmed with Control+Shift+Enter.

Working 100%, thank you. I saw examples of SUMPRODUCT elsewhere on line with ROW and OFFSET but could not adapt it to work for me. Thank you very much!

 

[Eric W]

You're welcome.