Count duplicate cells in a column based on another column

Question

Sample data:

Event    Client
 400      1
 400      1
 401      1
 402      2
 402      2
 402      2
 400      3
 401      3
 402      3
 403      3

This table represents one ticket to an event per row. I am trying to determine how many events a client attended (but I don't care how many tickets they bought to an event). The result I am trying to get:

Client 1: 2 events
Client 2: 1 event
Client 3: 4 events

I could write a Python script to determine this information, but I feel like there might exist an Excel function I can use to achieve the same result.

Suggestions?

Answer 1

Highlight your entire dataset and press "alt" then "a" then "m". That will remove duplicate entries, then a countif(B:B, B1) will count the number of entries for that specific client number.

Countif(B:B,1) will work for client 1 Countif(B:B,2) will work for client 2

But if you reference the cells they will be in (B1 and below) then drag down your formula it will work automatically.