CODEWITHSUNDEEP
google-sheetsfilteruniquegoogle-sheets-formulagoogle-sheets-query
mollying
mollying

Reputation: 1

Count unique values for unique dates in Google Sheets

I have a table that contains information on people who volunteered for multiple organizations over a one-month period. One person could have volunteered for the same organization on multiple dates, and multiple people could have volunteered on the same date. I'm interested in tracking, for each organization, unique volunteers (individuals who have volunteered at any point over the course of the month) and unique shifts (for the purposes of this data, a shift is one person volunteering on a unique date).

How can I pull the unique shifts metric? Here's an example of the type of data I'm dealing with:

Organization | Date       | Volunteer
A            | 10-01-2018 | Jane 
A            | 10-01-2018 | Ben
A            | 10-02-2018 | Jane
B            | 10-01-2018 | Emily
B            | 10-02-2018 | Jack

You can see that Organization A has 2 unique volunteers and 3 shifts, while Organization B has 2 unique volunteers and 2 shifts. I'm able to get the unique volunteer count by using countuniqueifs with the organization as the criterion, but to get the number of shifts I need both the "Date" and "Volunteer" columns to be unique, while keeping the organization as the criterion.

Upvotes: 0

Views: 749

Answers (1)

player0
player0

Reputation: 1

I'm interested in tracking, for each organization, unique volunteers 

=UNIQUE({A2:A, C2:C})

0

=QUERY(UNIQUE({A2:A, C2:C}), 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1 
  label count(Col2)''", 0)

0

(individuals who have volunteered at any point over the course of the month)

=QUERY(UNIQUE(FILTER({A2:A, C2:C}, MONTH(B2:B)=10)), 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1 
  label count(Col2)''", 0)

0

and unique shifts (for the purposes of this data, a shift is one person volunteering on a unique date)

=QUERY(UNIQUE(FILTER({A2:A, B2:B&C2:C}, MONTH(B2:B)=10)), 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1 
  label count(Col2)''", 0)

0

Upvotes: 1

Related Questions