I have three fields - Date Of Meeting, Location, Attendees. One Location per date, potentially multiple attendees (say, 1-5).
I'd like to know how many times a Location has been used over time, and also how many meetings each attendee has been to.
I've entered data initially as Date Of Meeting, Location, going back a couple of years. I then added a single Count field per meeting (value=1). This allowed me to create pivot table that lists the Locations and then sums the Counter field to see how many times that Location has been used.
(I think this is the best approach, there is likely something better).
My real question is - how should I add the Attendees to the raw data in order to also have a pivot table that shows how many meetings each attendee has been to?
I'm presuming that in the raw data worksheet I should add multiple rows, one per attendee, for each date/location, e.g.:
1/1, Location A, Bob
1/1, Location A, Jane
1/1, Location A, Pete
3/1, Location B, Bob
3/1, Location B, Jane
5/1, Location A, Jane
5/1, Location A, Pete
5/1, Location A, Steve
6/1, Location C, Bob
6/1, Location C, Pete
7/1, Location C, Bob
and so on.
But how do I get the counter field correct to be able to show not just :
Location A, 2 (times used)
Location B, 1
Location C, 2
but also
Bob, 4 (meetings attended)
Jane, 3
Pete, 3
Steve, 1
Any help appreciated. This is probably as dumb/simple a question as it sounds.
Bookmarks