i have had a play - using lots of helper columns with UNIQUE () , which you have as 365 version
Just to see how to build up a formula which works and , if you like , see the working - which i often do - before using things like sumproduct
I will also put on a share , dropbox , but only for a few days
Shared with Dropbox
www.dropbox.com
In column R
I have just used UNIQUE - to pull out the
Room, Name and date (but i used INT(date) to get rid of the time and put in column F
just so we can pull 3 consecutive columns D, E & F
=UNIQUE(D:F)
as i say this is just WIP - and maybe improved
Now I have used UNIQUE again in column V - to pull all the unique Names from the unique list
so now we can count how many times the names appear in list - column W
and then use your list to count how many names are 1 , 2 , 3 etc
Column Y
But i get different results to you and may be looking at this ALL wrong
for example
Acosta, Jose
has visited various rooms , from a filter on the list
GRAND BALLROOM A
NAPA C & D
NAPA C & D
NAPA C & D
NAPA C & D
SONOMA C & D
GRAND BALLROOM A
So thats 3 rooms
As has
Accad, Angelito
NAPA C & D
SONOMA C & D
NAPA C & D
NAPA C & D
GRAND BALLROOM A
GRAND BALLROOM A
GRAND BALLROOM A
GRAND BALLROOM A
So thats
3) Number of Attendees who visited 3 locations: 1
actually 2 and not 1
as i say i maybe missing something
Heres the numbers I get v yours
1) Number of Attendees who visited 1 location: 3 = 6
2) Number of Attendees who visited 2 locations: 0 = 6
3) Number of Attendees who visited 3 locations: 1 = 2
4) Number of Attendees who visited 4 locations: 0 = 0
5) Number of Attendees who visited 5 locations: 0 = 0
6) Number of Attendees who visited 6 locations: 0 = 0
7) Number of Attendees who visited 7 locations: 0 = 0
ROOMS-ATT-ETAF.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA |
---|
1 | D | S | T | Session Location | Full Name | date | Confirmation Number | Invitee Status | Session Check-In Date (GMT-08:00) Pacific [US & Canada] | Duration (Minutes) | Attendance Type | Checkout Date (GMT-08:00) Pacific [US & Canada] | Agenda Item Code | | | | | UNIQUE ROOMS , NAME AND DATE | | UNIQUE NAMES FROM OTHER UNIQUE | | | | |
---|
2 | 1 | 1 | 11 | GRAND BALLROOM A | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 7:17 | | OnArrival | 8/9/22 | 1111 | | | | | | | | | 0 | COUNT | | | | |
---|
3 | 1 | 2 | 11 | GRAND BALLROOM A | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1211 | | | | | Session Location | Full Name | date | | Full Name | | | 1) Number of Attendees who visited 1 location: 3 | 6 | |
---|
4 | 1 | 3 | 11 | GRAND BALLROOM A | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1311 | | | | | GRAND BALLROOM A | Abbott, Patrick | ###### | | Abbott, Patrick | 2 | | 2) Number of Attendees who visited 2 locations: 0 | 6 | |
---|
5 | 1 | 4 | 11 | GRAND BALLROOM A | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1411 | | | | | SONOMA C & D | Abbott, Patrick | ###### | | Abell, Ryan | 1 | | 3) Number of Attendees who visited 3 locations: 1 | 2 | |
---|
6 | 1 | 5 | 11 | GRAND BALLROOM A | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1511 | | | | | NAPA C & D | Abell, Ryan | ###### | | Abeyta, Raymond | 1 | | 4) Number of Attendees who visited 4 locations: 0 | 0 | |
---|
7 | 1 | 6 | 10 | SONOMA C & D | Abbott, Patrick | 8/9/22 0:00 | 6ZN6PS4JY59 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1610 | | | | | GRAND BALLROOM A | Abeyta, Raymond | ###### | | Accad, Angelito | 3 | | 5) Number of Attendees who visited 5 locations: 0 | 0 | |
---|
8 | 1 | 1 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | Planner Edit | | 1104 | | | | | NAPA C & D | Accad, Angelito | ###### | | Acevedo, Dominic | 2 | | 6) Number of Attendees who visited 6 locations: 0 | 0 | |
---|
9 | 1 | 2 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1204 | | | | | SONOMA C & D | Accad, Angelito | ###### | | Acevedo, Jose | 2 | | 7) Number of Attendees who visited 7 locations: 0 | 0 | |
---|
10 | 1 | 3 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1304 | | | | | GRAND BALLROOM A | Accad, Angelito | ###### | | Aceves, Danny | 1 | | | | |
---|
11 | 1 | 4 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1404 | | | | | GRAND BALLROOM B | Acevedo, Dominic | ###### | | Acosta, Jose | 3 | | | | |
---|
12 | 1 | 6 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1604 | | | | | NAPA A & B | Acevedo, Dominic | ###### | | Acosta, Roberto | 1 | | | | |
---|
13 | 1 | 7 | 4 | NAPA C & D | Abell, Ryan | 8/9/22 0:00 | 36N7QN3PMVQ | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1704 | | | | | GRAND BALLROOM A | Acevedo, Jose | ###### | | Acosta, Ruben | 1 | | | | |
---|
14 | 1 | 1 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1111 | | | | | SHOWROOM | Acevedo, Jose | ###### | | Adame, Kyle | 2 | | | | |
---|
15 | 1 | 2 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1211 | | | | | GRAND BALLROOM A | Aceves, Danny | ###### | | Adams, Brad | 1 | | | | |
---|
16 | 1 | 3 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1311 | | | | | GRAND BALLROOM A | Acosta, Jose | ###### | | Adams, Randy | 2 | | | | |
---|
17 | 1 | 4 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1411 | | | | | NAPA C & D | Acosta, Jose | ###### | | Adams, Richard | 2 | | | | |
---|
18 | 1 | 5 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1511 | | | | | SONOMA C & D | Acosta, Jose | ###### | | 0 | | | | | |
---|
19 | 1 | 6 | 11 | GRAND BALLROOM A | Abeyta, Raymond | 8/9/22 0:00 | 5LN64ZC4FLL | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1611 | | | | | GRAND BALLROOM A | Acosta, Roberto | ###### | | | | | | | |
---|
20 | 1 | 1 | 4 | NAPA C & D | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | | 1104 | | | | | SONOMA C & D | Acosta, Ruben | ###### | | | | | | | |
---|
21 | 1 | 1 | 10 | SONOMA C & D | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1110 | | | | | GRAND BALLROOM B | Adame, Kyle | ###### | | | | | | | |
---|
22 | 1 | 2 | 4 | NAPA C & D | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1204 | | | | | SHOWROOM | Adame, Kyle | ###### | | | | | | | |
---|
23 | 1 | 3 | 4 | NAPA C & D | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1304 | | | | | GRAND BALLROOM A | Adams, Brad | ###### | | | | | | | |
---|
24 | 1 | 4 | 11 | GRAND BALLROOM A | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1411 | | | | | GRAND BALLROOM B | Adams, Randy | ###### | | | | | | | |
---|
25 | 1 | 5 | 11 | GRAND BALLROOM A | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1511 | | | | | NAPA A & B | Adams, Randy | ###### | | | | | | | |
---|
26 | 1 | 6 | 11 | GRAND BALLROOM A | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1611 | | | | | SONOMA C & D | Adams, Richard | ###### | | | | | | | |
---|
27 | 1 | 7 | 11 | GRAND BALLROOM A | Accad, Angelito | 8/9/22 0:00 | 3FNT5242DTN | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1711 | | | | | SONOMA A | Adams, Richard | ###### | | | | | | | |
---|
28 | 1 | 6 | 2 | GRAND BALLROOM B | Acevedo, Dominic | 8/9/22 0:00 | 7SN6NK9FK8X | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1602 | | | | | 0 | 0 | 0 | | | | | | | |
---|
29 | 1 | 7 | 8 | NAPA A & B | Acevedo, Dominic | 8/9/22 0:00 | 7SN6NK9FK8X | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1708 | | | | | | | | | | | | | | |
---|
30 | 1 | 1 | 11 | GRAND BALLROOM A | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1111 | | | | | | | | | | | | | | |
---|
31 | 1 | 2 | 11 | GRAND BALLROOM A | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1211 | | | | | | | | | | | | | | |
---|
32 | 1 | 3 | 6 | SHOWROOM | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1306 | | | | | | | | | | | | | | |
---|
33 | 1 | 4 | 6 | SHOWROOM | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1406 | | | | | | | | | | | | | | |
---|
34 | 1 | 5 | 6 | SHOWROOM | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1506 | | | | | | | | | | | | | | |
---|
35 | 1 | 6 | 6 | SHOWROOM | Acevedo, Jose | 8/9/22 0:00 | 3TNLS2JZWBF | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1606 | | | | | | | | | | | | | | |
---|
36 | 1 | 1 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1111 | | | | | | | | | | | | | | |
---|
37 | 1 | 2 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1211 | | | | | | | | | | | | | | |
---|
38 | 1 | 3 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1311 | | | | | | | | | | | | | | |
---|
39 | 1 | 4 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1411 | | | | | | | | | | | | | | |
---|
40 | 1 | 5 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1511 | | | | | | | | | | | | | | |
---|
41 | 1 | 6 | 11 | GRAND BALLROOM A | Aceves, Danny | 8/9/22 0:00 | SHNR8B4YR8H | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1611 | | | | | | | | | | | | | | |
---|
42 | 1 | 1 | 11 | GRAND BALLROOM A | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1111 | | | | | | | | | | | | | | |
---|
43 | 1 | 2 | 4 | NAPA C & D | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1204 | | | | | | | | | | | | | | |
---|
44 | 1 | 3 | 4 | NAPA C & D | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1304 | | | | | | | | | | | | | | |
---|
45 | 1 | 4 | 4 | NAPA C & D | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1404 | | | | | | | | | | | | | | |
---|
46 | 1 | 5 | 4 | NAPA C & D | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1504 | | | | | | | | | | | | | | |
---|
47 | 1 | 6 | 10 | SONOMA C & D | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1610 | | | | | | | | | | | | | | |
---|
48 | 1 | 7 | 11 | GRAND BALLROOM A | Acosta, Jose | 8/9/22 0:00 | F7NBW4F5F32 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1711 | | | | | | | | | | | | | | |
---|
49 | 1 | 1 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1111 | | | | | | | | | | | | | | |
---|
50 | 1 | 2 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1211 | | | | | | | | | | | | | | |
---|
51 | 1 | 3 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1311 | | | | | | | | | | | | | | |
---|
52 | 1 | 4 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1411 | | | | | | | | | | | | | | |
---|
53 | 1 | 5 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1511 | | | | | | | | | | | | | | |
---|
54 | 1 | 6 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1611 | | | | | | | | | | | | | | |
---|
55 | 1 | 7 | 11 | GRAND BALLROOM A | Acosta, Roberto | 8/9/22 0:00 | X9NVGNP924G | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1711 | | | | | | | | | | | | | | |
---|
56 | 1 | 2 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1210 | | | | | | | | | | | | | | |
---|
57 | 1 | 3 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1310 | | | | | | | | | | | | | | |
---|
58 | 1 | 4 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1410 | | | | | | | | | | | | | | |
---|
59 | 1 | 5 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1510 | | | | | | | | | | | | | | |
---|
60 | 1 | 6 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1610 | | | | | | | | | | | | | | |
---|
61 | 1 | 7 | 10 | SONOMA C & D | Acosta, Ruben | 8/9/22 0:00 | T7N69SKRYG2 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1710 | | | | | | | | | | | | | | |
---|
62 | 1 | 3 | 2 | GRAND BALLROOM B | Adame, Kyle | 8/9/22 0:00 | 39NZLQ44BZ8 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1302 | | | | | | | | | | | | | | |
---|
63 | 1 | 5 | 6 | SHOWROOM | Adame, Kyle | 8/9/22 0:00 | 39NZLQ44BZ8 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1506 | | | | | | | | | | | | | | |
---|
64 | 1 | 3 | 11 | GRAND BALLROOM A | Adams, Brad | 8/9/22 0:00 | FWNR74N8TL9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1311 | | | | | | | | | | | | | | |
---|
65 | 1 | 2 | 2 | GRAND BALLROOM B | Adams, Randy | 8/9/22 0:00 | BJNLCQHR3CC | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1202 | | | | | | | | | | | | | | |
---|
66 | 1 | 3 | 2 | GRAND BALLROOM B | Adams, Randy | 8/9/22 0:00 | BJNLCQHR3CC | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1302 | | | | | | | | | | | | | | |
---|
67 | 1 | 4 | 8 | NAPA A & B | Adams, Randy | 8/9/22 0:00 | BJNLCQHR3CC | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1408 | | | | | | | | | | | | | | |
---|
68 | 1 | 2 | 10 | SONOMA C & D | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1210 | | | | | | | | | | | | | | |
---|
69 | 1 | 3 | 10 | SONOMA C & D | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1310 | | | | | | | | | | | | | | |
---|
70 | 1 | 4 | 10 | SONOMA C & D | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1410 | | | | | | | | | | | | | | |
---|
71 | 1 | 5 | 10 | SONOMA C & D | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1510 | | | | | | | | | | | | | | |
---|
72 | 1 | 6 | 3 | SONOMA A | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1603 | | | | | | | | | | | | | | |
---|
73 | 1 | 7 | 3 | SONOMA A | Adams, Richard | 8/9/22 0:00 | 8PNT5LT2YP9 | Accepted | 8/9/22 | | OnArrival | 8/9/22 | 1703 | | | | | | | | | | | | | | |
---|
74 | | | | | | | | | | | | | | | | | | | | | | | | | | | |
---|
|
---|