Count distinct Mondays, Tuesdays, etc...

MCorbin

I have a sheet that captures phone queue data by operator and date.

Date Operator # Calls Weekday (formula)

1/1 Bob 10 4

1/1 Sue 15 4

1/1 Ralph 10 4

1/8 Bob 11 4

1/8 Sue 16 4

I'm trying to create an average # of calls by day of the week. I have the date and a calculated field that gives me the weekday (2 for Monday, 3 for Tuesday, etc), but since I have multiple rows per day of the week (1 for each operator), I'm trying to get the number of DISTINCT Mondays, Tuesdays, etc...

I can SUMIF to get the total number of calls on a weekday, and I can countif to get the total number of rows on a weekday - but I want to get a count of distinct dates that fall on a given weekday.

So far I haven't been successful combining the function DISTINCT with COUNTIF or COUNTIFS.

Has anyone figured this formula out?

L_123

something like:

=count(distinct(collect(date:date,date:date,weekday(@cell) = 2

This would count how many mondays you received a call out of all of the dates in the date range. You can throw other criteria into the collect formula in order to parse it out further. Some things that come to mind are current year, and specific operator. Example below.

=count(distinct(collect(date:date,date:date,and(weekday(@cell) = 2,year(@cell)=year(today())),Operator:Operator,@cell = "Bob"

L_123

something like:

=count(distinct(collect(date:date,date:date,weekday(@cell) = 2

This would count how many mondays you received a call out of all of the dates in the date range. You can throw other criteria into the collect formula in order to parse it out further. Some things that come to mind are current year, and specific operator. Example below.

=count(distinct(collect(date:date,date:date,and(weekday(@cell) = 2,year(@cell)=year(today())),Operator:Operator,@cell = "Bob"

MCorbin

THANK YOU! I hadn't used Collect before, but that worked perfectly -

(I already had the weekday as a helper column)

=COUNT(DISTINCT(COLLECT({Date}, {Weekday}, 2)))