# Distinct Count if the value of another field is > 0

#### Edwardryez

##### New Member
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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### RickXL

##### MrExcel MVP
Hi,

Try this:

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

#### Edwardryez

##### New Member
Hi,

Try this:

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

Great Thank You

#### Edwardryez

##### New Member
Solved Thank You RickXL

#### RickXL

##### MrExcel MVP
No problem.

It is not exactly an obvious formula is it?

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

Regards,

##### MrExcel MVP
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:
Rich (BB code):
``````
=SUM(IF(FREQUENCY(IF(C2:C233<>"",IF(H2:H233<>"",
MATCH("~"&C2:C233,C2:C233&"",0))),ROW(C2:C233)-ROW(C2)+1),1))
``````

#### RickXL

##### MrExcel MVP

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

Regards,

#### RickXL

##### MrExcel MVP
... 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.

##### MrExcel MVP

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

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

##### MrExcel MVP
Thanks for reassurance.

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

Replies
14
Views
484
Replies
9
Views
297
Replies
1
Views
328
Replies
1
Views
311
Replies
17
Views
778

1,195,953
Messages
6,012,515
Members
441,703
Latest member
clivelincoln

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back