Count distinct Mondays, Tuesdays, etc...
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
-
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"
Answers
-
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"
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!