Count of distinct values with error

Romano el Polako

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

Scott Peters

Hi @Romano el Polako - Because E-Entry 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 auto-number column or some other guaranteed unique identifier?

Romano el Polako

Hey,

Yes, I have auto-number column, " Smart_Audit_ID "

Thanks,

Romano

Scott Peters

@Romano el Polako - Apologies, you actually don't need the unique auto-number 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([E-Entry Number]:[E-Entry Number], [#Discrepancy_Helper]:[#Discrepancy_Helper], "YES")))

For a good explanation of how this works, check out https://community.smartsheet.com/discussion/71185/count-unique-values-if-a-criteria-is-met

I hope this helps!