Count Unique Entries Multiple Criteria

Jennifer Lindquist
Jennifer Lindquist ✭✭✭
in Formulas and Functions

I would like to count the number of unique employees who have submitted an entry per quarter.

Here's the formula I tried:

=COUNT(DISTINCT(COLLECT(Employee:Employee, YRQtr:YRQtr, YRQtr@row, [Idea Approved/Denied]:[Idea Approved/Denied], "Approved")))

Always returns a value of 1.

Screenshot 2024-10-09 103810.jpg

· Share on FacebookShare on Twitter

Best Answer

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    Answer ✓

    Hi @Jennifer Lindquist,

    I just tested out your formula and it worked perfectly for me:

    Screenshot from 2024-10-10 13-51-53.png 
    =COUNT(DISTINCT(COLLECT(Employee:Employee, YRQtr:YRQtr, YRQtr@row, Status:Status, "Approved")))

    Can you try creating a second sheet with just a couple rows and columns, and see if the formula works for you on a different sheet? Maybe something is going on with your main sheet that we need to figure out.

    Cheers!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter

Answers

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    Answer ✓

    Hi @Jennifer Lindquist,

    I just tested out your formula and it worked perfectly for me:

    Screenshot from 2024-10-10 13-51-53.png 
    =COUNT(DISTINCT(COLLECT(Employee:Employee, YRQtr:YRQtr, YRQtr@row, Status:Status, "Approved")))

    Can you try creating a second sheet with just a couple rows and columns, and see if the formula works for you on a different sheet? Maybe something is going on with your main sheet that we need to figure out.

    Cheers!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter
  • Jennifer Lindquist
    Jennifer Lindquist ✭✭✭
    edited 10/10/24

    It did work in a new sheet. Odd.

    So… I ended up referencing the current sheet to a new sheet and got it to work. Thank you, but I really wish it would've worked on the original.

    image.png

    · Share on FacebookShare on Twitter
  • SSFeatures
    SSFeatures ✭✭✭✭✭

    You're welcome! You can try deleting your YrQtr column and your Column10, then recreating those columns and re-adding the data back in. This is a super weird bug though!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!