Count duplicate values
This tutorial shows how to count duplicate values in a range using an Excel formula, with the COUNTIF function, or VBA
Example: Count duplicate values
|
=COUNTIF(B5:B9,D5)
|
This formula uses the Excel COUNTIF function to return the number of cells, from range (B5:B9), that are equal to the value captured in cell D5 (15). The first part of the COUNTIF function is where you enter the range from which you want to count the number of duplicate values. The second part of the COUNTIF function is were you enter the value for which you are want to find the number of occurrences in a specified range. In this example we are referencing to a cell (D5) that captures a value for which the formula will find the number of occurrences in a specified range. The formula has identified two occurrences of this value captured in cells B6 and B8.
|
Sub Count_duplicate_values_using_formula()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the number of times the value in cell ("D5") is captured in range ("B5:B9")
ws.Range("E5").Formula = "=COUNTIF(B5:B9,D5)"
ws.Range("E5").Formula = "=COUNTIF(B5:B9,D5)"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Data Range: Select the range that you want to check for duplicates by changing the range reference ("B5:B9") in the VBA code.
Duplicate Value: Select the cell that captures the value for which you want to check the number of time it occurs in the specified range, by changing the cell reference ("D5") in the VBA code or changing the value in cell ("D5").
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Data Range: Select the range that you want to check for duplicates by changing the range reference ("B5:B9") in the VBA code.
Duplicate Value: Select the cell that captures the value for which you want to check the number of time it occurs in the specified range, by changing the cell reference ("D5") in the VBA code or changing the value in cell ("D5").
METHOD 2. Count duplicate values using VBA
VBA
Sub Count_duplicate_values()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
ws.Range("E5") = ws.Application.WorksheetFunction.CountIf(ws.Range("B5:B9"), ws.Range("D5"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Data Range: Select the range that you want to check for duplicates by changing the range reference ("B5:B9") in the VBA code.
Duplicate Value: Select the cell that captures the value for which you want to check the number of time it occurs in the specified range, by changing the cell reference ("D5") in the VBA code or changing the value in cell ("D5").
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Data Range: Select the range that you want to check for duplicates by changing the range reference ("B5:B9") in the VBA code.
Duplicate Value: Select the cell that captures the value for which you want to check the number of time it occurs in the specified range, by changing the cell reference ("D5") in the VBA code or changing the value in cell ("D5").
EXPLANATION
This tutorial shows how to count duplicate values in a range, using an Excel formula and VBA.
The Excel formula uses the COUNTIF formula to count the number of duplicate values in a range. Enter the range from which you want to count the number of duplicate values into the first component of the COUNTIF function. The value for which you want to count the number of occurrences from the selected range would be the criteria of the COUNTIF function.
This tutorial shows two VBA methods that can be applied to count duplicate values in a range. The first VBA method uses the Formula property (in A1-style) with the same formula that is used in the Excel method.
The second VBA method uses the COUNT function to return the number of occurrences that a value is repeated in a specified range.
The second VBA method uses the COUNT function to return the number of occurrences that a value is repeated in a specified range.
FORMULA
=COUNTIF(range, dup_value)
=COUNTIF(range, dup_value)
ARGUMENTS
range: Range of cells to test for duplicate values.
dup_value: The value to check for duplicates in the specified range.
range: Range of cells to test for duplicate values.
dup_value: The value to check for duplicates in the specified range.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Highlight cells with duplicate values | How to highlight cells that contain duplicate values in a selected range using Excel and VBA | |
| Find duplicate values in a range | How to find duplicate values in a range using Excel and VBA | |
| Count cells if greater than | How to count cells that are greater than a specific value using Excel and VBA | |
| Count cells if less than | How to count cells that are less than a specific value using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria |