Count matches between two columns that also got a certain text in another column

[JakkeJakobsen]

In A6:A18 there are some names, which will be found in Z2:Z41. I want it to count of many of these that match and also has text A, B or C in C6:C18.

Name	Rank		List1	List2
Mr1	Member		Mr1	Mr2
Mr2	Officer		Mr4	Mr3
Mr3	Trialist		Mr6	Mr5
Mr4	Social
Mr5	Member
Mr6	Trialist

<tbody>
</tbody>

So basically, what I want it to is count how many in "Name" (Column A) is found in "List1" (Column Z) that also got the rank from "Rank" (column B) as "Member", "Officer" or "Trialist". In other words, all but "Social".

I will also have one for "List2" (Column AA), but I can get that myself ofc, I just need a formula to get started from.

And yes, sadly enough, this is for a game ^^

Jim

 

[jbeaucaire]

Something like this:

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7, Z2:Z4, 0)))*(B2:B7<>"Social"))