Counting rows that contain any one item listed in another row

[Sergio Dutra]

Hi, I have the following table:

Area Person Members
X Jack Jack
Y Mary Peter
Z Jack
X Peter
X Mary
Z Mary

I want to count the number of Persons who are Members and have entries in
Area X. So in the example above it should return 2 (Jack and Peter have X
entries and are Members but Mary is not a member though she has X entry).

How to do this in Excel?

 

[Satti Charvak]

Hi Sergio,

This is what i did,
create a new column "count"

and put the below formula for getting the count of the name that is obey you
2 specification.

=SUMPRODUCT(($A$2:$A$7="X")*($B$2:$B$7=C2))

Finally i took the sum total of the "count" column to get the total no of
entries.

Please click on "yes" if this answer has helped you.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A2:A7="x"),--(ISNUMBER(MATCH(B2:B7,C2:C3,0))))