39

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.

There's no function =COUNTDISTINCT(A2:A100) so what can I do instead? (Excel 2003)

I can't exactly use answers to this similar question because I don't want to modify the table or the filtering. I need an addition in the worksheet, not a modification.

Community
  • 1
Torben Gundtofte-Bruun
  • 18,336

8 Answers8

38
=SUMPRODUCT((A2:A100 <> "")/COUNTIF(A2:A100,A2:A100 & ""))

will do it without having to use an array formula.

Lance Roberts
  • 8,653
  • 4
    How/why does this work??? – RBarryYoung Apr 25 '13 at 20:10
  • 5
    @RBarryYoung, SUMPRODUCT works like an Array function without being one. While it can do much more than what it's being used for here, here it is just checking every instance of a column, and coming up with a scaled value for each distinct value based on how many iterations there are in the column. So if there were two instances of 'a', it would add 1/2 and 1/2 to get 1. So for every distinct value, you'll add one to the sum. – Lance Roberts Apr 25 '13 at 20:30
  • 1
    This is the part that I'm having trouble with: "coming up with a scaled value for each distinct value based on how many iterations there are in the column." If I understand this, then SUMPRODUCT is effectively executing a loop like for each [i] in A2:A100 DO:{ sum += (([i]<>"")/COUNTIF(A2:A100,[i])) }, right? I am having a little trouble understanding how it knows to enumerate both the conditional and the 2nd COUNTIF parameter, but *not* the 1st COUNTIF parameter? Is this DWIM-magic documented/explained anywhere by MS? It would be nice to know how to write these intentionally. – RBarryYoung Apr 25 '13 at 21:41
  • 1
    Yes, you got it, it iterates an array for what would normally be a single value, multiplying all of its parameters (which in this case is just 1), then adding the iterations. SUMPRODUCTs can get very complex, but they are one of the most useful functions in Excel, it can do literal miracles. – Lance Roberts Apr 25 '13 at 21:47
  • 4
    This fails if the input contains blank entries. Easy fix as demoed on youtube is this: =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). It ensures that the COUNTIF never returns a 0 for blank lines, which would result in a DIV/0 error. – cfi Jul 04 '13 at 09:19
  • 1
    %? The percent character does not work in Excel 2010 for appending text. It is the ampersand & that does the trick. – cfi Jul 08 '13 at 07:48
  • Does not work properly with columns containing timestamps. It works fine with just dates tho. – Stefan Jan 13 '14 at 16:39
  • So much googling, and finally found it. Thank you! – russds Dec 11 '15 at 22:42
  • This is great but I can't make use of it in filtered views. – Joel Jun 25 '20 at 21:48
  • It works fine as long a the size of the range is below 1000 – Vivian De Smedt Oct 29 '20 at 18:21
8

I found a solution here which seems to be an incredible roundabout way to solve it. But hey, it works...

=SUM(IF(COUNTIF(A2:A100,A2:A100)=0, "", 1/COUNTIF(A2:A100,A2:A100)))

and then press Ctrl+Shift+Enter. Pressing only Enter will give the wrong result.

Excellll
  • 12,717
Torben Gundtofte-Bruun
  • 18,336
  • I'd love to see other answers! Is there a better way than this to solve it? – Torben Gundtofte-Bruun Sep 17 '10 at 14:16
  • 1
    Nope, that's the best way. I'm not sure why there's an IF there. You can just use =SUM(1/COUNTIF(A2:A100,A2:A100)) array entered – dkusleika Sep 17 '10 at 16:07
  • 2
    @dkusleika The IF seems to be there to prevent a division by zero error. As mentioned on the page linked to above, this is needed if the range contains blank cells, otherwise you can leave the IF out. – Bavi_H Sep 19 '10 at 00:17
3

This article shows this for text values:

=SUM(IF(FREQUENCY(IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""), IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""))>0,1))

and this for numeric values:

=SUM(IF(FREQUENCY(C3:C25, C3:C25)>0,1))

This article shows similar formulas, but also shows a method using filters.

Count the number of unique values by using a filter

You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

  1. Ensure that the first row in the column has a column header.
  2. On the Data menu, point to Filter, and then click Advanced Filter.
  3. In the Advanced Filter dialog box, click Copy to another location.
  4. If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data.
  5. In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values.

  6. Select the Unique records only check box, and click OK.

    The unique values from the selected range are copied to the new column.

  7. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter:
    =ROWS(B1:B45)

Dennis Williamson
  • 108,309
2

=SUM(1/COUNTIF(A2:A100;A2:A100))

Confirm with Ctrl+Shift+Enter

For each cell, it counts how many times it occurs, and summes the inverses of all these values. Suppose some string or number occus 5 times. Its inverse is 0.2 which get summed 5 times, so 1 is added. In the end it gives the number of different values.

Note: doesn't work when blanks occur!

user221470
  • 21
  • 1
1

In Excel 365, you can use

=COUNTA(UNIQUE('Sheet 1'!C1:C100))

to count the unique values.

Function Explanation:

  1. UNIQUE('Sheet 1'!C1:C100,) returns the distinct values from the range C1 to C100.
  2. COUNTA() takes these unique values as input and returns the count.
matrix
  • 437
1

Found two resources for you:

http://www.excelforum.com/excel-worksheet-functions/365877-count-distinct-values.html

and

http://www.cpearson.com/excel/Duplicates.aspx

You should be able to find a workable solution from there.

Sux2Lose
  • 3,327
0

Try this link. This shows how to count unique values in a list omitting blank cells.

http://www.functioninexcel.com/lists-arrays/count-unique-values-in-a-list/

= sum( if( frequency( match( List , List , 0 ) , match( List , List , 0 )) > 0 , 1 ))

Where "List" is your range of cells for example:

List = $A$2:$A$12 OR- List = offset($A$1,,,match( rept("z",255) , $A:$A )) -OR- List = offset($A$1,,,match( value(rept("9",255)) , $A:$A ))

Charles Hunt
  • 1
  • 1
0

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

Be sure to hit CONTROL+SHIFT+ENTER after pasting this formula. This is for a range of A2:A100, adjust the range accordingly.

Vlada
  • 1