CODEWITHSUNDEEP
google-sheetscountduplicatesgoogle-sheets-formulaarray-formulas
jwhite
jwhite

Reputation: 13

Count number of users in different scenarios across different columns in Google Sheets

I have a spreadsheet with ClientIDs, SessionIDs and FormIDs where Client is a website user, The SessionID is a specific visit they made to the website and a FormID is generated for each form they complete in each session.

I'm trying to extract the number of users who fit into the following scenarios:

My attempt to solve this is here (Attempt tab): https://docs.google.com/spreadsheets/d/1K2iJAkt9m0a0xhjkkQiLylvHjLX5bXYlwdX2f7XUwO0/edit?usp=sharing

I'm using a helper column to count the unique occurences in each column, then using COUNTIFS to work out what scenario each clientID belongs to e.g Scenario 1 =(COUNTIFS(B:B,">1", D:D, ">1", F:F, "=1")).

I've tried different methods of COUNT and COUNTUNIQUE however I can't seem to find a formula/method the dedupes the clientIDs as scenarios 2-4 have the clientID on multiple rows that correspond with the other columns.

COUNTIFS works perfectly for scenario 1 because each ClientID only has 1 row, but not the others.

The first tab of the spreadsheet has the raw data in case I'm overlooking a better way of approaching this (I had tried concatenating the data and pulling out ClientID that way however due to the removal of the rounding on the concatenate that didn't work either).

Thank you for any help with this, it's greatly appreciated.

Upvotes: 1

Views: 166

Answers (1)

player0
player0

Reputation: 1

0

cell F2:

=ARRAYFORMULA(COUNTIFS(
 COUNTIF(A2:A, A2:A), 1, 
 COUNTIF(B2:B, B2:B), 1, 
 COUNTIF(C2:C, C2:C), 1))

cell F3:

=ARRAYFORMULA(COUNTIFS(
 COUNTIF(A2:A, A2:A), ">"&1, 
 COUNTIF(B2:B, B2:B), ">"&1, 
 COUNTIF(C2:C, C2:C), 1, 
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), 1))

cell F4:

=ARRAYFORMULA(COUNTIFS(
 COUNTIF(A2:A, A2:A), ">"&1, 
 COUNTIF(B2:B, B2:B), 1, 
 COUNTIF(C2:C, C2:C), ">"&1, 
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), 2))

cell F5:

=ARRAYFORMULA(COUNTA(IFERROR(FILTER(A2:A, 
 COUNTIF(A2:A, A2:A)>1, 
 COUNTIF(B2:B, B2:B)=1, 
 COUNTIF(C2:C, C2:C)=1, 
 COUNTIFS(C2:C, C2:C, ROW(C2:C), "<="&ROW(C2:C))=1, 
 COUNTIFS(A2:A, A2:A, ROW(C2:C), "<="&ROW(C2:C))>=2))))

Upvotes: 0

Related Questions