Distinct Count

[hakuna]

Hi seniors!

I have a big excel file with alot of data in it. area where i need help will be in counting the distinct number of project numbers. as in, if there are more than 2 cases where the project numbers are same, then i should count them as 1 and not separately.

please help!

 

[Vidar]

Hi

This is a well known array formula to count unique elements in a list.
=SUM(1/COUNTIF(A2:A10,A1:A10))
It's an array formula so hit Ctrl+Shift+Enter to confirm the formula.
Change the range (A1:A10) to suit your needs.

Vidar

 

[Aladin Akyurek]

Hi seniors!

I have a big excel file with alot of data in it. area where i need help will be in counting the distinct number of project numbers. as in, if there are more than 2 cases where the project numbers are same, then i should count them as 1 and not separately.

please help!

Let A2:A1000 house project numbers/ids.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$1000<>"",
  MATCH("~"&$A$2:$A$1000,$A$2:$A$1000&"",0)),
   ROW($A$2:$A$1000)-ROW($A$2)+1),1))

If you are certain that there are no spacila meaning chars like < surrounding the target entries, the "~"& and &"" bits can be removed from the above formula.

 

[taurean]

If the project numbers are numeric and in 1 column say A1:A1000 Then
=SUM((FREQUENCY(A1:A1000,A1:A1000)>0)*1)

However, if they are alpha numeric then above formula won't work so use:
=SUM((FREQUENCY(MATCH(A1:A1000,A1:A1000,0),MATCH(A1:A1000,A1:A1000,0))>0)*1)

 

[Aladin Akyurek]

Hi

This is a well known array formula to count unique elements in a list.
=SUM(1/COUNTIF(A2:A10,A1:A10))
It's an array formula so hit Ctrl+Shift+Enter to confirm the formula.
Change the range (A1:A10) to suit your needs.

Vidar

This formula, even if not corrected for empty/blank cells, while short, is not that efficient:

See:

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

 

[hakuna]

thanks everyone.

let me try out, and will soon get back!

 

[Vidar]

This formula, even if not corrected for empty/blank cells, while short, is not that efficient:

See:

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

HI Aladin

Didn't know it was slower. Thanks for the link.

Vidar