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))
[TABLE="class: grid, width: 631"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[/TR]
[TR]
[TD]Participant #[/TD]
[TD]Peer[/TD]
[TD]Team Member[/TD]
[TD]Senior Colleague[/TD]
[TD][/TD]
[TD]Participant[/TD]
[TD]# of Feedback received[/TD]
[/TR]
[TR]
[TD]Participant 1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]Participant 1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Participant 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Participant 3[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Participant 4[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Participant 5[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[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.
[TABLE="class: grid, width: 390"]
<tbody>[TR]
[TD]Participant[/TD]
[TD]Relationship[/TD]
[TD][/TD]
[TD]Partipant[/TD]
[TD]# of feedback[/TD]
[/TR]
[TR]
[TD]PP1[/TD]
[TD]Team Member[/TD]
[TD][/TD]
[TD]PP1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]PP1[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP1[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP1[/TD]
[TD]Team Member[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP1[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP2[/TD]
[TD]Team Member[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP2[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP2[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP2[/TD]
[TD]Peer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col></colgroup>[/TABLE]