Count Unique Entries Multiple Criteria
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.
Best Answer
-
I just tested out your formula and it worked perfectly for me:
=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) (LinkedIn)
SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: — Auto Sorting — Sorting with Filters — Report PDF Generation — Copy and Paste Conditional Formats — Copy and Paste Automation Workflows — Column Manager — and so many more.
Answers
-
I just tested out your formula and it worked perfectly for me:
=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) (LinkedIn)
SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: — Auto Sorting — Sorting with Filters — Report PDF Generation — Copy and Paste Conditional Formats — Copy and Paste Automation Workflows — Column Manager — and so many more.
-
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.
-
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) (LinkedIn)
SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: — Auto Sorting — Sorting with Filters — Report PDF Generation — Copy and Paste Conditional Formats — Copy and Paste Automation Workflows — Column Manager — and so many more.
Help Article Resources
Categories
Check out the Formula Handbook template!