CODEWITHSUNDEEP
google-sheetsunique
Rover
Rover

Reputation: 407

Counting unique values in Google Spreadsheet based on multiple columns

Let's take this spreadsheet for example:

ID | StoreName | StoreID | CheckinTime  | User
0  | w1        | 1       | 10:00        | user1
1  | w5        | 1       | 10:01        | user2
2  | w2        | 1       | 10:01        | user1
3  | w1        | 1       | 10:01        | user4
4  | w5        | 1       | 10:05        | user1
5  | w3        | 1       | 10:05        | user6
6  | w1        | 1       | 10:05        | user1
7  | w1        | 1       | 10:05        | user1

Is there a way to create a new column/tab/sheet to count all the unique checkins for a store. So let's say; StoreName "w1" is visited by "user1" 3 times and 1 time by "user4". The expected output will be 2 (2 unique visitors for "w1"). This is the output I would like to have:

ID | StoreName | uniqueCheckins
0  | w1        | 2
1  | w2        | 1
2  | w3        | 1
3  | w4        | 0
4  | w5        | 2

Upvotes: 0

Views: 187

Answers (2)

Akshin Jalilov
Akshin Jalilov

Reputation: 1716

Updated my answer

=({FILTER(SORT(UNIQUE(Index(UNIQUE({B2:B,E2:E}),,1))),SORT(UNIQUE(Index(UNIQUE({B2:B,E2:E}),,1)))<>""),ARRAYFORMULA(COUNTIF(INDEX(UNIQUE({B2:B,E2:E}),,1),"="&FILTER(SORT(UNIQUE(Index(UNIQUE({B2:B,E2:E}),,1))),SORT(UNIQUE(Index(UNIQUE({B2:B,E2:E}),,1)))<>"")))})

Upvotes: 1

AdamL
AdamL

Reputation: 24599

To produce the StoreName and uniqueCheckins output columns:

=QUERY(QUERY(B:E,"select B, E, count(C) group by B, E",1),"select Col1, count(Col2) group by Col1 label count(Col2) 'uniqueCheckins'",1)

However this will omit any StoreName that doesn't appear in the raw data (in your example, w4). Would this be OK?

Upvotes: 1

Related Questions