COUNTIF but only for unique values

[RodneyC]

I need to modify this formula:

=COUNTIF(Data4Adcel!$R:$R,">0") so that it only counts unique values in Data4Adcel!$D:$D where Data4Adcel!$R:$R,">0"

Said another way, in column D, there may be 7 entries that say purple and 5 of them have values in R >0 and there may be 5 values in column D that say orange and 4 of them have values in R >0. the formula would show a result of 2. FYI, Column D could have hundreds of different values.

Thank you in advance

 

[Anthony47]

For example:

=SUM(IFERROR(1/COUNTIF(Data4Adcel!D1:D100,Data4Adcel!D1:D100)*(Data4Adcel!R1:R100<>""),0))

But this farmula cannot deal with whole columns, you must specify an ending row

 

[RodneyC]

For example:

=SUM(IFERROR(1/COUNTIF(Data4Adcel!D1:D100,Data4Adcel!D1:D100)*(Data4Adcel!R1:R100<>""),0))

But this farmula cannot deal with whole columns, you must specify an ending row

THANKS! this =SUM(IFERROR(1/COUNTIF(Data4Adcel!$D$1:$D$1000,Data4Adcel!$D$1:$D$1000)*(Data4Adcel!$R$1:$R$1000<>""),0)) worked perfectly.

How would I modify it i I also wanted to add the criteria of Data4Adcel!$N:$N,"*Development*" I have tried this:

=SUM(IFERROR(1/COUNTIF(Data4Adcel!$D$1:$D$1000,Data4Adcel!$D$1:$D$1000)*(Data4Adcel!$N$1000:$N$1000,"*Development*",Data4Adcel!$N$1000:$N$1000,"*Development*")*(Data4Adcel!$R$1:$R$1000<>""),0)) but that produces an error message.

 

[Anthony47]

Add a new criteria:

=SUM(IFERROR(1/COUNTIF(Data4Adcel!D1:D1000,Data4Adcel!D1:D1000)*(Data4Adcel!R1:R1000<>"")*(FIND("Development",Data4Adcel!N1:N1000)>0),0))

beware that it is CASE Sensitive

 

[RodneyC]

This: =SUM(IFERROR(1/COUNTIF(Data4Adcel!D1:D1000,Data4Adcel!D1:D1000)*(Data4Adcel!R1:R1000<>"")*(FIND("Development",Data4Adcel!N1:N1000)>0),0)) is producing a value of .48 sorting it manually I see the actual value should be 1. Any ideas on why it's off?

thanks for the continued help

 

[Anthony47]

The formula should count the cells that "include" that text, in any position.
Test the formula on a shorter list and try to get the point
Or share sample data to be used for our testing

 

[RodneyC]

I appreciate your help. I'm going to have to step away for the rest of the day but will try and figure this out over the weekend.