Using a more advance formula than CountIF

[Soni123456]

Hi all,

I was wondering if someone could help. I have a list of Participants (Participant 1 down to Participant 30), and each participant has received feedback from a number of their colleagues (Peers, Team Member, Senior Colleagues etc.). What I am looking to do is to use a formula whereby I could count the number of Peers (for example) have given feedback to that particular participant. I want to be able to enter "Participant 3" into a cell and then the formula look up that participant in the data table range and give me back the number of peers that have given him/her feedback.

I hope this makes sense.

Please let me know if you would like more information.

Thanks in advance

 

[godsaaint]

You can do something like this:

I put an x to simulate someone that provided feeback.

Formula in G2: =COUNTA(INDEX(B2:D6,MATCH(F2,A2:A6,0),0))

Col A	Col B	Col C	Col D	Col E	Col F	Col G
Participant #	Peer	Team Member	Senior Colleague		Participant	# of Feedback received
Participant 1	x	x			Participant 1	2
Participant 2			x
Participant 3	x	x	x
Participant 4		x	x
Participant 5		x

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[Soni123456]

Hello,

Thank you for your help. What is the data was laid out this way

Participant Relationship
PP1 Team Member
PP1 Peer
PP1 Peer
PP1 Team Member
PP1 Peer
PP2 Team Member
PP2 Peer
PP2 Peer
PP2 Peer

and I only wanted to count the number of Peers that gave feedback to PP1?

Thanks again

 

[godsaaint]

Alright:

Formula in E2: =SUMPRODUCT((A2:A10=D2)*(B2:B10="Peer"))

I used "Peer" directly in the formula. If you'd rather, you can reference a cell instead to easily change between counting peers, team members, etc.

Participant	Relationship		Partipant	# of feedback
PP1	Team Member		PP1	3
PP1	Peer
PP1	Peer
PP1	Team Member
PP1	Peer
PP2	Team Member
PP2	Peer
PP2	Peer
PP2	Peer

<tbody>
</tbody><colgroup><col><col><col span="2"><col></colgroup>