Count unique values in a column in Excel

by

To count the number of different values in A2:A100 (not counting blanks):

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

Copied from an answer by @Ulli Schmid to What is this COUNTIF() formula doing?: 

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

Counts unique cells within A1:A100, excluding blank cells and ones with an empty string (“”).

How does it do that? Example: 

A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]

so this &”” is needed to turn blank cells (-) into empty strings (“”). If you were to count directly using blank cells, COUNTIF() returns 0. Using the trick, both “” and – are counted as the same:

COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]

If we now want to get the count of all unique cells, excluding blanks and “”, we can divide

(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]

by our intermediate result, COUNTIF(A1:A100,A1:A100&””), and sum up over the values.

SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4

Had we used COUNTIF(A1:A100,A1:A100) instead of COUNTIF(A1:A100,A1:A100&""), then some of those 0/94 would have been 0/0. As division by zero is not allowed, we would have thrown an error.

More Related Contents:

  1. TEXTJOIN for xl2010/xl2013 with criteria
  2. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  3. Check if the file exists using VBA
  4. Difference between date and time w/out work week Excel
  5. How can I create a progress bar in Excel VBA?
  6. Combine 2 Excel tables into one appending the data?
  7. Compare double in VBA precision problem
  8. Row count on the Filtered data
  9. Excel VBA Macro: User Defined Type Not Defined
  10. Automate IE via Excel to fill in a dropdown and continue
  11. Run-Time error 1004 Excel 2013
  12. Pasting an Excel range into an email as a picture
  13. Excel VBA to Export Selected Sheets to PDF
  14. VBA (Excel) Dictionary on Mac?
  15. How to represent a DateTime in Excel
  16. Refresh Excel VBA Function Results
  17. excel VBA run macro automatically whenever a cell is changed
  18. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  19. Translate text using vba
  20. Concatenate column headers if value in rows below is non-blank
  21. Sending mail using Outlook where the Send method fails
  22. Using a variable for row in (row,column) for a range in VBA
  23. Using the value in a cell as a cell reference in a formula?
  24. Unicode string literals in VBA
  25. How to open a workbook specifying its path
  26. Excel, save and close after run
  27. Generate N random numbers whose sum is a constant K – Excel
  28. How do I read data from a spreadsheet using the OpenXML Format SDK?
  29. Delete all data rows from an Excel table (apart from the first)
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment