Distinct Count if the value of another field is > 0

[Edwardryez]

Trying to modify the formula below:
{= SUM(IF(C2:C233<>"",1/COUNTIF(C2:C233,C2:C233)))}
Based on the Values in the column:
H2:H233<>""

How can i put this together?

 

[RickXL]

Hi,

Try this:

=SUM(IFERROR(NOT(ISBLANK(H2:H233))/COUNTIFS(C2:C233,C2:C233,H2:H233,"<>"&""),0))

 

[Edwardryez]

Hi,

Try this:

=SUM(IFERROR(NOT(ISBLANK(H2:H233))/COUNTIFS(C2:C233,C2:C233,H2:H233,"<>"&""),0))

Great Thank You

 

[Edwardryez]

Solved Thank You RickXL

 

[RickXL]

No problem.

It is not exactly an obvious formula is it?

Anyway, I am glad it worked.
Thanks for letting me know.

Regards,

 

[Aladin Akyurek]

Trying to modify the formula below:
{= SUM(IF(C2:C233<>"",1/COUNTIF(C2:C233,C2:C233)))}
Based on the Values in the column:
H2:H233<>""

How can i put this together?

COUNTIF(S) is not very efficient to use in such a task...

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(C2:C233<>"",IF(H2:H233<>"",
    MATCH("~"&C2:C233,C2:C233&"",0))),ROW(C2:C233)-ROW(C2)+1),1))

 

[RickXL]

Hi Aladin,

Being sceptical, I compared the two formulas. I extended the range to C2:C10000 to make it obvious.
On my PC, my formula took about 15 seconds and yours took about 2 seconds!

OK, I am convinced
Your link looks interesting as well so thank you for your input.

Regards,

 

[RickXL]

... and if I use a timer and time just the worksheet mine takes 14.8 seconds and yours takes about 0.00026 for 9,999 rows.

I think that must be on the verge of a clear victory.

 

[Aladin Akyurek]

Hi Aladin,

Being sceptical, I compared the two formulas. I extended the range to C2:C10000 to make it obvious.
On my PC, my formula took about 15 seconds and yours took about 2 seconds!

OK, I am convinced
Your link looks interesting as well so thank you for your input.

Regards,

... and if I use a timer and time just the worksheet mine takes 14.8 seconds and yours takes about 0.00026 for 9,999 rows.

I think that must be on the verge of a clear victory.

Thanks for reassurance.

 

[RickXL]

Thanks for reassurance.

No problem, if I have managed to re-assure someone with over 68,000 posts then my job is done ...