How to count duplicate values in a column in Excel?

In Excel, you can use COUNTIF function to count the duplicates.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range "$A$2:$A$9" indicates the list of data, and "A2" represents the cell you want to count the frequency. You can adjust these as needed), then press "Enter", and drag the fill handle to fill the column you need. See screenshot:

Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the "Column A" indicates column of data, and "A2" represents the cell you want to count the frequency. You can adjust these as needed).

But if you want to identify the order of the occurrence of the duplicates, you can use the following formula.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A2,A2) (the range "$A$2:$A2" indicates the list of data, and "A2" represents the cell you want to count the order. You can adjust these as needed), then press "Enter", and drag the fill handle to fill the column you need. See screenshot:

Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch count the occurrences of each items in a column (the Fruited Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below:

1. Select the table containing the column where you will count each duplicate, and click "Kutools" > "Merge & Split" > "Advanced Combine Rows".

2. In the "Advanced Combine Rows", select the column you will count each duplicate and click "Primary Key", next select the column you will place counting results in and click "Calculate" > "Count", and then click the "OK" button. See screenshot:

And now it has counted the occurrence of each duplicate in the specified column. See screenshot:

Count merged cells in Excel

Count blank cells or nonblank cells in a range in Excel