Count distinct Mondays, Tuesdays, etc...

Question

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 · Accepted Answer

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"