Counting unique entries across two columns where cell in second column is a particular value

Ken Morse

I've looked at a lot of answers on here and tried a few formula's but have not been successful. Below is a simple example.

I want to get a distinct count of the number of Apps that have Option 1, i.e., the first two rows count as 1 not 2.

So, answer should be unique App/Option 1 count is 5 and unique App/Option 2 is 2

Any help greatly appreciated have tried all manner of count/distinct/collect options to no avail
Thanks!

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT combo.

=COUNT(DISTINCT(COLLECT({App Range}, {Option Range}, @cell = "Option 1")))

Ken Morse

Perfect, thanks Paul!

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT combo.

=COUNT(DISTINCT(COLLECT({App Range}, {Option Range}, @cell = "Option 1")))

Ken Morse

Perfect, thanks Paul!

Paul Newcome

Happy to help. 👍️