Count distinct Mondays, Tuesdays, etc...

MCorbin
MCorbin Overachievers Alumni

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?

Best Answer

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    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)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!