Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells with text not listed. | Excel Worksheet Functions | |||
How to select a listed item? | Excel Worksheet Functions | |||
How to select a listed item? | Excel Discussion (Misc queries) | |||
Counting transactions as listed in reports | Excel Discussion (Misc queries) | |||
Delete rows listed less than 8 times??? | Excel Worksheet Functions |