excelexcel-formula

Count Distinct Values in one column based on text in another column

I want to count distinct values in the ServerName column if the value in the Ticket column is "Yes".

I can count the distinct values in ServerName by using the formula:

=COUNTA(UNIQUE(A2:A9))

But now I want to count distinct values in the first column only if they have a value in the second column. In the example below, I would expect the count to be 3.

I've tried a couple of formulas like

=COUNTIFS(A2:B9,"Yes")

but nothing seems to return the correct value.

Any thoughts on how to count these values correctly?

enter image description here

Solution

  • Using ROWS, UNIQUE, and FILTER:

    =ROWS(UNIQUE(FILTER(A2:A9,B2:B9="Yes")))

    enter image description here