UDF: Need help with creating Countif based UDF

[mabolotin]

All,

I am new to UDFs and need to create one for the following formula:

=IF(COUNTIF($D2:D$20000,D2)=1,(COUNTIF(D:D,D2)/COUNTIF(D:D,D2)),"0")

Any help would be greatly appreciated.

[TMS]

Please Login or Register  to view this content.

will return a value of 1, and "0" is a text string, not the value 0.

[mabolotin]

I understand that and it is the intended outcome. Background: I have a unique identifier which I need tagged for unique instances; my formula does that by tagging 1 or 0 (do not need value). Because my data set is growing, and the amount of unique IDs created is increasing, I need to create a UDF which will help me shorten having to write out the formula. So essentially i just want to be able to input "=uniqueinstance(d2:d20000)" or something along those lines.

[TMS]

What about this bit:

Please Login or Register  to view this content.

The first $D2 does not have the row fixed. Is that intentional? Is D2000 meant to be fixed or do you want the UDF to determine the last row?

[mabolotin]

It is indeed intentional and ideally I would like the UDF to determine the last row; 20000 is just a placeholder at this point but the data set will exceed that in a few months time.

[TMS]

I think a UDF is overkill given that

Please Login or Register  to view this content.

is redundant

You can just use this:

Please Login or Register  to view this content.

And, if you create a Dynamic Named Range, for example, Col_D, defined as:

Please Login or Register  to view this content.

You can then have:

Please Login or Register  to view this content.

Note that, when you create the DNR, you MUST be on row 2 because the row, as in $D2, will reflect the current row

That seems pretty short to me, and much more convenient than having to make your workbook macro enabled just so you can have a UDF.