count unique elements

[EPMMGR06]

rTrying to count unique data in a column that contains mixed numbers & text.
Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What
is the corect formula for this?

 

[Luke M]

See the XL help file article "Count unique values among duplicates" for the
various functions. I'm guessing yours is similar to:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

Note that all 3 of the formulas listed in the article are array formulas,
and thus need to be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[Domenic]

rTrying to count unique data in a column that contains mixed numbers & text.
Have a Sum(If(Frequency(Match) formula but I must have copied it wrong. What
is the corect formula for this?

Try...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10
)-ROW(A2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[Bernd P]

Worksheet functions?

Choose your own approach of "waiting for Godot":
http://sulprobil.com/html/count_unique.html

Regards,
Bernd

 

[Ashish Mathur]

You could try this non-formula approach. Give a heading to the column and
then select the range including the headers to assign it a name, say Range1.
Now convert it to a list (Ctrl+L). Now save the file Click on any blank
cell and go to Data > Import External data > New Database query. Select
Excel files and click on Next. On the last screen, select the radio button
for Edit query in MS Query. Cluck on Finish. Now go to Edit > Properties
and check the box for unique records. Now go to file and select the last
option - Return data to MS Excel. Now click on the properties button and
select the box for refresh every 1 minutes. Click on Finish. You will now
have all the unique entries. Now that you have the unique records


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com