Count the number of distinct subsets of a string that match a criteria

Nick DeMarco

I need to create a formula to count the number of distinct subsets of a string that match a criteria.

For example.

I have a sheet with a column foo. the foo column contains the following

A-Z

A-Y

A-Z

A-X

A-Y

B-A

B-B

C-D

C-D

I need a formula that looks at this column and returns the number of distinct A(s) which would be three in my example. One for A-Z, one for A-Y and one for A-X.

Thanks in advance,

Nick

Mark Cronk

Hi @Nick DeMarco ,

Try this:

=COUNT(DISTINCT(COLLECT(FOO:FOO, FOO:FOO, FIND("A", @cell) = 1)))

It should work for your example. But, it is hard coded for your example and the logic may not work for your real use case.

Help at all?

Mark

Mark Cronk

Hi @Nick DeMarco ,

Try this:

=COUNT(DISTINCT(COLLECT(FOO:FOO, FOO:FOO, FIND("A", @cell) = 1)))

It should work for your example. But, it is hard coded for your example and the logic may not work for your real use case.

Help at all?

Mark

Nick DeMarco

Mark,

Thank you! This was exactly what I needed!

Regards,

Nick

Mark Cronk

Happy to help. Thanks for using the Communuty.

Mark