Count duplicate values in order
This tutorial shows how to count duplicate values in order using an Excel formula, with the COUNTIF function, or VBA
Example: Count duplicate values in order
|
=COUNTIF($B$5:$B5,B5)
|
This formula uses the Excel COUNTIF function with the first part of the range being formatted as an absolute reference and the second part only having the column reference as an absolute reference. Cell B5 represents the first cell in the range.
|
Sub Count_duplicate_values_in_order()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count only positive numbers
For x = 5 To 11
For x = 5 To 11
ws.Range("C" & x) = Application.WorksheetFunction.CountIf(ws.Range(ws.Cells(5, 2), ws.Cells(x, 2)), ws.Cells(x, 2))
Next x
End Sub
ADJUSTABLE PARAMETERS
Output Column: Select the output column by changing the column reference ("C") in the VBA code.
Output and Data Rows: In this example the output row reference is the same as the associated data row reference. Therefore, you can select the output rows and the rows that form part of a range from which you want to count duplicate values in order by changing the x values (5 to 11) in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count duplicate values in order by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Column: Select the output column by changing the column reference ("C") in the VBA code.
Output and Data Rows: In this example the output row reference is the same as the associated data row reference. Therefore, you can select the output rows and the rows that form part of a range from which you want to count duplicate values in order by changing the x values (5 to 11) in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count duplicate values in order by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to count duplicate values in order using an Excel formula, with the COUNTIF function, or VBA.
Both the Excel and VBA methods make use of the COUNTIF function to count the duplicate values, from a selected range, in order.
FORMULA
=COUNTIF(range,cell)
=COUNTIF(range,cell)
ARGUMENTS
range: A dynamic range with the first part of the range being formatted as an absolute reference and the second part being formatted as an relative reference.
cell: The cell that you want to count for duplicate values.
range: A dynamic range with the first part of the range being formatted as an absolute reference and the second part being formatted as an relative reference.
cell: The cell that you want to count for duplicate values.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count duplicate values | How to count duplicate values in a range using Excel and VBA | |
| Find duplicate values in a range | How to find duplicate values in a range using Excel and VBA | |
| Highlight cells with duplicate values | How to highlight cells that contain duplicate values in a selected 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 methods |
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 |