Counting Unique values in one column based on criteria in another

[RodneyC]

I'm musing this formula

=COUNTA(UNIQUE(Data4Adcel!D2:D344))

and it works fine but I need to add another criteria and can't seem to figure it out. I need it to only count the unique values in D where the values in R are greater than 0.

thanks in advance

 

[Sufiyan97]

Try

=COUNTA(UNIQUE(FILTER(Data4Adcel!D2:D344,Data4Adcel!D2:D344>0)))

 

[RodneyC]

=COUNTA(UNIQUE(FILTER(Data4Adcel!D2:D344,Data4Adcel!D2:D344>0)))

I changed the last values to R2:R344>0 and it worked perfectly, THANK YOU!

 

[RodneyC]

I'm trying to add one more criteria now... count it only if the values in N2:N1000 contain the word Development. I've tried this

=COUNTA(UNIQUE(FILTER(Data4Adcel!$D$2:$D$1000,Data4Adcel!$R$2:$R$1000>0,Data4Adcel!N2:N1000,"*Development*"))) and get an error saying there are too many arguments.

 

[Sufiyan97]

DELETED

 

[kevin9999]

=COUNTA(UNIQUE(FILTER(Data4Adcel!D2:D1000,(Data4Adcel!R2:R1000>0)*(ISNUMBER(SEARCH("Development",Data4Adcel!N2:N1000))))))

 

[Fluff]

I would use Rows rather than counta, as counta will return 1 even if there are no matches

=IFERROR(ROWS(UNIQUE(FILTER(Data4Adcel!D2:D1000,(Data4Adcel!R2:R1000>0)*(ISNUMBER(SEARCH("Development",Data4Adcel!N2:N1000)))))),0)