Formula to count distinct values in a column that contain a certain text string

Options
Heidi D
Heidi D ✭✭
edited 09/20/22 in Formulas and Functions

I am looking for a way to count distinct values in a column, but only if the values contain a certain text string. For example, in my Fruit column, I'd like to count the unique values that contain "Large." In this case, the number should be 3.

Fruit

Apple - Small

Apple - Medium

Apple - Large

Orange - Small

Orange - Medium

Orange - Medium

Orange - Large

Pear - Small

Pear - Medium

Pear - Large

Pear - Large

I've tried =COUNT(DISTINCT(Fruit:Fruit)), but that would give me all the distinct values, or 9.

I've tried =COUNTIF(Fruit:Fruit, CONTAINS("Large", @cell)), but that gives me all the instances of "Large," or 4.

Any ideas?

Thanks!

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!