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

Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!