Formula in F2 is:

=COUNTIFS(C:C,E2,B:B,"<>Closed/Lost")

I would like it to return number of unique values from Column A, so desired output is 3

CodePudding user response：

You can try below formula-

=COUNTA(UNIQUE(FILTER(A2:A6,C2:C6=E2)))

In case of possibility of no match with criterial then use-

=SUM(--(UNIQUE(FILTER(A2:A6,C2:C6=E2,""))<>""))

To include Status column use-

=SUM(--(UNIQUE(FILTER(A2:A6,(C2:C6=E2)*(B2:B6<>"Closed/Lost"),""))<>""))