Correct Way To "COUNTUNIQUE" That Only Counts Once

user_3311163 0
Viewed 130

I am trying to count the unique values of a column, based on their status in another column, example:

Customers License Active
Adam Yes
Barry No
Adam No
Claire No

In this situation, I want to know how many customers have at least 1 active license, and how many customers do not have at least one active license.

The formula I have tried is: =COUNTUNIQUEIFS(A2:A,B2:B,"Yes") This returns 1 in this situation which is correct, as there is 1 customer who has a Yes on column B. My issue is when I try to do the reverse, count the "No" using this formula: =COUNTUNIQUEIFS(A2:A,B2:B,"No") it returns 3 which is not the desired result as it is counting the second Adam as a unique value too because they have a "No" in column B.

The result I want here is 2, because Adam has a yes somewhere in column B so I don't want him counted again the next time his field is counted.

google-sheets-formula google-sheets
2 Answers

It seems to me that the easiest way to get the "No" count is like this:

=COUNTUNIQUE(A2:A)-COUNTUNIQUEIFS(A2:A,B2:B,"Yes")

It's even easier if you've already pulled the "Yes" count to a cell (say, C2), in which case the "No" count could be gained quite simply with this:

=COUNTUNIQUE(A2:A)-C2

user_139032830

I don't think you can do it in a single step - try filtering out those with at least one "Yes" like this:

=countunique(filter(A2:A,countifs(B2:B,"Yes",A2:A,A2:A)=0))

enter image description here

Explanation

When a countifs has a range instead of a single value in its criteria part countifs(B2:B,"Yes",A2:A,A2:A) , the countifs gets re-evaluated for each cell in the range. So you get an array with the results of

countifs(B2:B,"Yes",A2:A,A2)
countifs(B2:B,"Yes",A2:A,A3)
countifs(B2:B,"Yes",A2:A,A4)
countifs(B2:B,"Yes",A2:A,A5)

and so on all the way down the columns.

The first countifs above checks right through a2:a and b2:b to see if there are any cases where the name is Adam and the license condition is true and gets a count of 1 so that row is filtered out. The same thing happens in the next row containing Adam (row 4) - the countifs checks right through both columns excluding the headers and the count is still 1 so that row is filtered out as well leaving just Barry and Claire.

If you wanted to exclude all records containing "Test" in the Customer column, You could add a condition to the filter using the multiplication operator to 'AND' it with the existing condition:

=countunique(filter(A2:A,(countifs(B2:B,"Yes",A2:A,A2:A)=0)*(A2:A<>"Test")))

If you had several names to exclude, you would probably want to make a list of them and use a lookup to stop the formula getting too long and unwieldy, but it would be the same idea.

user_38949170
Related