I have a data table with multiple columns.
I need to count the number of occurrences of names in the range --> Summary!$C$3:$D$10000.
The name list is located on Sheet1!$A$2:$A$8
I want the total count to be Sheet1!$B$2:$B$8
If the same name is shown in the adjacent column cell, I only want to count that name once.
It is this part which is causing me a headache.
I can count all occurrences in the entire range, using
.
When a name occurs twice on the same row, the formula obviously doens't take that into account.
This is the result I need the formula show based on the above data set:
Chris Peacock = 4
Bob Smith = 2
Dave Jones = 2
Ryan Evans = 2
Ivor Biggun = 2
Mike Hunt = 2
Peter Schlong = 2
Oscar Reynolds = 2
Can someone give me a hand to solve this issue please?
Thanks
I need to count the number of occurrences of names in the range --> Summary!$C$3:$D$10000.
The name list is located on Sheet1!$A$2:$A$8
I want the total count to be Sheet1!$B$2:$B$8
If the same name is shown in the adjacent column cell, I only want to count that name once.
It is this part which is causing me a headache.
I can count all occurrences in the entire range, using
Excel Formula:
=COUNTIF(Summary!$C$3:$D$10000,Sheet1!$A2)
When a name occurs twice on the same row, the formula obviously doens't take that into account.
C | D |
Bob Smith | Dave Jones |
Dave Jones | Bob Smith |
Ryan Evans | Ivor Biggun |
Chris Peacock | Mike Hunt |
Ivor Biggun | Peter Schlong |
Mike Hunt | Oscar Reynolds |
Chris Peacock | Chris Peacock |
Peter Schlong | Ryan Evans |
Oscar Reynolds | Chris Peacock |
Chris Peacock | Mike Hunt |
This is the result I need the formula show based on the above data set:
Chris Peacock = 4
Bob Smith = 2
Dave Jones = 2
Ryan Evans = 2
Ivor Biggun = 2
Mike Hunt = 2
Peter Schlong = 2
Oscar Reynolds = 2
Can someone give me a hand to solve this issue please?
Thanks