I need help with a formula to determine the number of days a certain criteria has appear in a table.

Christopher Hornsby

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.

Mark Cronk

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

Mark Cronk

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

Christopher Hornsby

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.

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark