Counting Cells without repeat

**Squirrel** · 08-29-2011, 08:19 PM

I have a spreadsheet set up where I input a lot of data into a raw sheet and need to get some stats off of it. The problem I have is, I don't know how to count correctly. For example, I have a column for Name, Event, Time Start, Time End. In the name column one person will come up multiple times. I have been able to count how many time a name comes up, but what I want to do is count how many times a name comes up but only once per unique event.

Example is attached to the thread.

Basic question:
How do I count the amount of unique events a person has attended?

Worth noting is that there are consistently more rows added by the week or day when events happen.

**Marcol** · 08-29-2011, 09:01 PM

Your profile states you are using 2003, but your posted sample is 2007 or later.

See if this workbook is on the track you are looking for. (Based on 2003)
Sort your data by "Name" (Column A)

**Squirrel** · 08-29-2011, 09:02 PM

Sorry my profile is old, I have 2007 now that I am using at my home computer, not sure what the office computer is. I'm sure I can adapt it if it doesn't work. I haven't had any trouble yet. I'll take a look and let you know. Thanks.

**MarvinP** · 08-29-2011, 09:06 PM

Hi Squirrel,

See if the attached does what you want. Pivots are easy to make and Refresh. Does my example give you the info you need?

**Marcol** · 08-29-2011, 09:07 PM

If you don't need this to work with pre-2007 versions, have a look at COUNTIFS(), it's a tad more efficient.

**Squirrel** · 08-29-2011, 09:07 PM

That is very close to what I am looking for but actually too detailed. Although I will keep it for reference in case we also go that route. What I was looking for an output would be something like:

Tom Green : 2 Appearances, 1 Event
Tim Green: 1 Appearance, 1 Event
Sam Red: 2 appearances, 2 events

So in a table on a different sheet it would be:
NAMES // APPEARANCES // EVENTS // HOURS

(if you look at my other thread that was solved, I will be using this same technique to add the amount of hour each person has at events)

Thanks.

**Squirrel** · 08-29-2011, 09:12 PM

In response to MarvinP..

A pivot table does look nice with the data, it was a little different from my goals but close. I don't know much about pivot tables but apparently I should learn more. If you read my above reply it looks at roughly what my goal is. I will also be getting stats based on group... example below..

NAME // GROUP (1,2,3,4) // APPEARANCES // EVENTS // HOURS

And then separate stats on

APPEARANCES per group, EVENTS per group, HOURS per group... and further breaking this down by month because on a separate sheet I have each event's information and the date it was.

I don't know if anyone can solve all of these or just point me in the right direction... any help is appreciated including what has already been added.

**Marcol** · 08-29-2011, 09:52 PM

Just for fun have a look at this 2007 and later workbook.

Apologies, to much beer tonight ...

...

**MarvinP** · 08-29-2011, 09:57 PM

How about...

**Squirrel** · 08-29-2011, 10:05 PM

These are great help. I will show it around tomorrow and play with it. If anyone else comes up with anything let me know. These will all be a help to me.