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

Heidi D

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!

Jeff Reisman

@Heidi D

try adding in a COLLECT:

=COUNT(DISTINCT(COLLECT(Fruit:Fruit, Fruit:Fruit, CONTAINS("Large", @cell))))