# How to count unique values

This workbook uses a custom function to return the count of equal values in two different ranges. The function counts matches regardless of the position in the range. You will also find a function that counts the matches according to the position in the range.

Updated: 2000-08-08 & 2012-04-11 Requires: XL5 File size: 29 kB

# Count unique values

The custom function below counts the unique values only from a given range. This function uses the fact that a collection can only hold items with unique identifications keys. If you try to add an item with a key that already exists in the collection, an error will occur.

```Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function```

Example, count the unique values in the range A1:A100:
=CountUniqueValues(A1:A100)

# Count Function Examples

In Excel you can count the content of cells that meet different criteria in several different ways. These are some of the functions that can be used:

• COUNT
Counts the number of cells in a range that contains numeric values.
• COUNTA
Counts the number of nonblank cells in a range.
• COUNTBLANK
Counts the number of blank cells in a range.
• COUNTIF
Counts the number of cells in a range that meet a given criteria.
• An array formula can also be useful in cases that none of the above functions
are suitable.
• DCOUNT
This database-function requires values stored in a table with columnheadings and a separate criteria range to describe which values are to be counted.

The following examples demonstrate the COUNT functions listed above. The range address may be replaced with a defined name (e.g. MyRange). In this example a semicolon (;) is used as the list separator, repace with a comma (,) if necessary.

Description Formula
Counts the values in the range E4:E23 =COUNT(E4:E23)
Counts all non-blank cells in the range E4:E23 =COUNTA(E4:E23)
Counts blank cells in the range E4:E23 =COUNTBLANK(E2:E23)
Counts all values greater than 0 =COUNTIF(E4:E23;">0")
Counts all values less than 0 =COUNTIF(E4:E23;”<0″)
Counts all cells containing the text yes =COUNTIF(F4:F23;"yes")
Counts all cells containing text starting with k =COUNTIF(F4:F23;”k*”)
Counts all cells containing text with the letter a =COUNTIF(F4:F23;”*a*”)
Counts all cells containing the text yes or no =COUNTIF(F4:F23;"yes")+COUNTIF(F4:F23;"no")
Same as above with an array formula {=SUM((F4:F23="yes")+(F4:F23="no"))}
Counts all cells containing 3 characters =COUNTIF(F4:F23;”???”)
Counts all cells with values between 30 and 80 =COUNTIF(E4:E23;”>=30″)-COUNTIF(E4:E23;”>80″)
Counts all unique numbers in the range =SUM(IF(FREQUENCY(E4:E23;E4:E23)>0;1;0))
Counts all odd numbers in the range (array formula) {=SUM(MOD(G4:G23;2))}
Counts all even numbers in the range (array formula) {=SUM(IF(MOD(G4:G23;2)=0;1;0))}
Counts cells with an AND-criteria (array formula) {=SUM(IF((F4:F23="yes")*(G4:G23>10);1;0))}
Counts cells with an AND-criteria (array formula) {=SUM((F5:F24="yes")*(G5:G24>10))}
Counts cells with two AND-criterias (array formula) {=SUM((F5:F24="yes")*(G5:G24>10)*(A5:A24="North"))}
Counts cells with an OR-criteria (array formula) {=SUM((F4:F23="yes")+(F4:F23="no"))}

# Count cells in a range from all worksheets

It’s possible to achieve this with the built-in COUNT() or COUNTA() function, but that can be a big task if the workbook contains many worksheets, or if you frequently add new worksheets to the workbook. To count a range from several worksheets you can use the COUNTA-function like this:

`=COUNTA(Sheet1:Sheet10!A1:A100)`

The formula above will count all non-blank cells in the range A1:A100 in the worksheets Sheet1 to Sheet10.

With the custom function below it’s easy to count cells in a range from all the worksheets in a workbook:

```Function CountAllWorksheets(InputRange As Range, InclAWS As Boolean) As Double
' counts the content of InputRange in all worksheets in the workbook
Dim ws As Worksheet, TempCount As Long
Application.Volatile ' calculates every time a cell changes
TempCount = 0
For Each ws In ActiveWorkbook.Worksheets
If InclAWS Then ' include the active worksheet
Else ' exclude the active worksheet
If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
End If
End If
Next ws
Set ws = Nothing
CountAllWorksheets = TempCount
End Function```

This function is used in the same way as built-in worksheetfunctions. In Excel 5/95 the variable InputRange must be defined as Variant instead of Range. This function can easily be adapted for use with other worksheetfunctions that you wish to use on all the worksheets in a workbook.

# Count By Color

With the built-in COUNTA()-function it’s not possible to count cells in a range depending on each cells background color. With the custom function below you can count the cells depending on their background color:

```Function CountByColor(InputRange As Range, ColorRange as Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function```

This function is used in the same way as built-in worksheetfunctions. InputRange
is the range that the function is going to count, ColorRange is a reference to a cell
containing the background color you wish to count.
E.g. used like this in a worksheet formula:

`=CountByColor(A1:A100;C1)`

In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range.
This function can easily be adapted for use with other worksheetfunctions that you wish to use on cells with different background colors.