I need help with a formula to determine the number of days a certain criteria has appear in a table.
I am new to smartsheet and trying to help our quality team. I need a formula that will count the number of unique days that an auditor has entered audits. I have one sheet that is the complete database with an automatically created date for every audit and another row that list which auditor created the entry. In another sheet, I have a list of each auditor's name that I am trying to use as the criteria for the count. But I am trying to piece together how to combine a distinct date formula with a countif formula for the auditor's name. The end goal here is to determine the average number of audits that each auditor does per day by dividing the total count of audits for each auditor by the number of days the auditor has entered audits.
Best Answer
-
Hi,
Your formula will be along the lines of:
=COUNT(DISTINCT(COLLECT(date:date, auditor:auditor, auditor@row)))
Replace my date:date and auditor:auditor with the column names you use. They can be external ranges.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi,
Your formula will be along the lines of:
=COUNT(DISTINCT(COLLECT(date:date, auditor:auditor, auditor@row)))
Replace my date:date and auditor:auditor with the column names you use. They can be external ranges.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you. I figured out that the reason it was not originally working for me was because the distinct formula was only recognizing the year. I had to add a column that turned into a text string to get it to recognize the whole date. It is fully functional now and will be foundation for a much larger base of information.
-
Excellent. Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!