Counting within groups

[Dan]

I have a set of data, i.e. as follows (paste into Excel to view)
Respondent Feeding Back On Relationship
John Smith John Smith Self
Barry Roberts John Smith Manager
Bill Edwards John Smith Colleague
Carla Jenson John Smith Colleague
Peter Flatmel Emma Wilson Self
Ian Thompson Emma Wilson Manager
Veronica Short Emma Wilson Colleague

In the example, two people (John Smith and Emma Wilson) have a number
of respondents to them in a questionnaire. Their respondents
relationship to them is known, and the two main participants also give
feedback on themselves (hence their name is listed twice).

I have a requirement that both John and Emma need to have 1 self
questionnaire, 1 manager questionnaire and 2 Colleague questionnaires
completed.

How could I use Excel in the above example to highlight that John
Smith has met the requirements but Emma hasn't?

Thanks,

Dan

 

[Jarek Kujawa]

surely there is a simpler way, but try insert this array-formula
(insert with CTRL+SHIFT+ENTER) in E1:

=IF(SUM(IF(($B$1:$B$7=D1)*(($C$1:$C$7="Self")),1))=1,1,0)+IF(SUM(IF(($B
$1:$B$7=D1)*(($C$1:$C$7="Manager")),1))=1,1,0)+IF(SUM(IF(($B$1:$B$7=D1)
*(($C$1:$C$7="Colleague")),1))=2,2,0)=4

with your data in range A1:C7
D1="John Smith"
D2="Emma Wilson"

copy down from E1 to E2

pls click YES if it helped

HIH