Counting rows that contain any one item listed in another row
 

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?

Counting rows that contain any one item listed in another row
 

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.
--

Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"Sergio Dutra" wrote:

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?

Counting rows that contain any one item listed in another row
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Sergio Dutra" wrote in message
...
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?