Hi MM Lines

I am not quite sure, if i understand you exactly, but this works for
me:

=SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="Y ES"))

Your Target cell should not be in the Range you search, that's why i
new changed it to M1.

Hope that makes sense, otherwise just ask

Carlo