Count unique values in Excel 2

[dmposey61]

I am trying to count the number of unique values in a column. The column format is "general". The array is named "POLICY". I tried using {=sum(1/countif(policy,??))} but I don't know what the put after POLICY?

I have a column that shows account numbers 11-11-1111-1 and there are duplicates. I need to know the total number of accounts excluding the duplicates in that column.

Help??
dmposey61

 

[VBAjedi]

Easiest way I can think of would be to use the Advanced Filter with the "Unique" option selected, then use the SUBTOTAL function to count the visible rows. . .

VBAjedi

 

[NumberCrunchingMonky]

Put policy in as your second data range. As a general rule, I "fool proof" the formula to ignore blank cells, as they'll limit the formula:

So, your formula would be:

{=SUM(IF(COUNTIF(POLICY,POLICY)=0," ",1/COUNTIF(POLICY,POLICY)))}

NMC

 

[macropod]

Hi dmposey61,

To get a count of unique values, you could use:
=SUMPRODUCT((POLICY<>0)/COUNTIF(POLICY,POLICY))
to exclude empty cells and 0 values, or:
=SUMPRODUCT((POLICY<>"")/COUNTIF(POLICY,POLICY))
to include empty cells and 0 values.

Cheers

 

[Hcleslie]

I have a similar need for a formula that counts unique visible cells. The data range could be either text or values.

Thanks

HCLeslie

 

[byundt]

macropod,
I get a #DIV/0! error if the list contains a blank. But if you concatenate an empty string, you can avoid it:
=SUMPRODUCT((POLICY<>"")/COUNTIF(POLICY,POLICY & ""))
Brad

 

[Hcleslie]

Brad, thank you for your reply.
I used your formula and it did give me the correct count of the total unique names, but when I turned on autofilters and selected 1 name, the value still displayed the total unique numbers instead of 1. Only visible is one of the items that I am looking for.

Thanks again,

Charlie

 

[xlbo]

Yup - only formula that doesn't count hidden rows is SUBTOTAL. You will need code either in the form of a sub or a udf.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question