Count Text String Occurrence Across 2 Column Array But Only Count Text String Once if in Adjacent Cell

bearwires

Board Regular
Joined
Mar 25, 2008
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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
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 SmithDave Jones
Dave JonesBob Smith
Ryan EvansIvor Biggun
Chris PeacockMike Hunt
Ivor BiggunPeter Schlong
Mike HuntOscar Reynolds
Chris PeacockChris Peacock
Peter SchlongRyan Evans
Oscar ReynoldsChris Peacock
Chris PeacockMike 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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
One way:
Rich (BB code):
=COUNTIF(Summary!$C$3:$D$10000,Sheet1!$A2)-COUNTIFS(Summary!$C$3:$C$10000,Sheet1!$A2,Summary!$D$3:$D$10000,Sheet1!$A2)
 
Upvote 0

Forum statistics

Threads
1,215,061
Messages
6,122,921
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top