Count of distinct values with error
Hello SmartHeads!
I hope you are doing great!
I have another question regarding formulas.
There are two columns:
"E  Entry Number"
 Important: values in this column can duplicate
Two:
"#Discrepancy_Helper"
This is a helper column checking whether or not there is an error in the row.
Desired outcome:
I have to calculate count of Distinct Entry Numbers with at least 1 Discrepancy (value "Yes")
I have already calculated distinct count of entry numbers, but I don't know how to end up with desired value.
Do you have any ideas?
Thanks,
Roman
Answers

Hi @Romano el Polako  Because EEntry Number could have duplicates, you will need at least one more column in your sheet that makes the row unique. Do you already have one, like an autonumber column or some other guaranteed unique identifier?

Hey,
Yes, I have autonumber column, " Smart_Audit_ID "
Thanks,
Romano

@Romano el Polako  Apologies, you actually don't need the unique autonumber column for this solution. I was thinking you were asking for something else. You could apply this formula in a column formula or in a Sheet Summary field:
=COUNT(DISTINCT(COLLECT([EEntry Number]:[EEntry Number], [#Discrepancy_Helper]:[#Discrepancy_Helper], "YES")))
For a good explanation of how this works, check out https://community.smartsheet.com/discussion/71185/countuniquevaluesifacriteriaismet
I hope this helps!
Help Article Resources
Categories
Check out the Formula Handbook template!