Count Unique Attendees For Each Event

Beginner

About this Tutorial

Find out how many people attended each event even if they are double counted in attendee lists.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNTUNIQUE() COUNTUNIQUEIFS() FILTER() SUM() UNIQUE()

Video Transcript

<div>0:00 Sometimes when we have an attendee list and we want to count the number of attendees in the list, we will use something called count unique and we can do count unique , counts unique values in a range , we can find out how many attendees Total like they have the unique names. 0:18 So we go A2 colon A and we can see that there are six attendees here. Now the issue is let's say we wanted to filter the attendees. 0:36 And we wanted to know how many attendees per each event. But sometimes our data is very dirty, right?

We might have multiple attendees, similar names, same names. 0:46 Umm, we might have taken attendance. It's at the beginning and end of an event. And so we wanna get only the unique values per each event. 0:56 Uh, and you might wanna start thinking about a convoluted sort of some and a filter . But we can do it all. 1:04 One formula and that's count unique ifs. And this is really cool because you can select a range . We're gonna select the name range here. 1:14 And our criteria range is gonna be b. And then we're gonna select one of the, uh, events.

But we can select it through a drop down menu here. 1:24 So we're just gonna select d3 here. So d3 here. And now we see three attendees for the opening ceremonies. Our unique look we have, Bob up here. 1:37 We have opening ceremonies and we see opening ceremonies here. Bob. So this helps us keep our data very clean because if we had just checked off, hey counted how many opening ceremonies there are we've had four but we really only have three unique attendees. 1:52 We can select a different event closing ceremonies and now this is automatic.

So this D3 is changing based on the input here in D3 of this drop down menu. 2:04 And so for each and every event we can see how many unique So round one you see there's four round ones. 2:10 We might have counted four here but we now see there's Avery twice here. So this helps us analyze our data a lot more in depth and very easily especially with this drop down menu. 2:22 Or if you were doing something know like listing each of the events we could create events here.

Let's call events and we'll use unique to grab all the names of the events we'll start with beats. 2:41 And then we're going to have to transpose this. So if we just hit enter we have all of these here. 2:45 Uh but we can also put them across with transpose. If we want but let's get the unique count of attendees for each one of these. 2:56 Let's move this down a little. So this is the event and this is the attendee count. So we're going to use the same exact count unique ifs here. 3:10 But instead of d3. gonna select f here and we're not gonna have to move anything.

We just copy and paste down and we see the attendee count for each event here. 3:23 So two ways to display the same data if you're looking for something dynamic that we need to select that we want. 3:28 You want see, maybe analyze one event at a time or if you want to see all the events and compare them uniquely with the the unique attendees. 3:37 This is how you do it here. This is helpful for you to count unique . uh attendees for an event or really cool unique count unique F's here.</div>