How to Count Repeated Values and Display Count Beside Last Occurrence in Excel
Question details
The user needs a spreadsheet formula that identifies duplicate values in a column, counts their total occurrences, and outputs the final count exclusively beside the last appearance of each repeated value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset with multiple duplicate entries where the user wants to summarize the total frequency of each item without cluttering the worksheet with repeated counts on every row.
- Observed behavior
- The intended output is to display blank cells for all initial and intermediate occurrences of a value, and only display the total count on the row containing the final occurrence.
Ensure you clearly define the range of your dataset and verify whether your data contains blank rows, as these can sometimes disrupt advanced array formulas. Sorting your data is optional for these formulas but can make visually verifying the results much easier.
Use Standard COUNTIF Formula (Compatible with All Versions)
This is the most reliable and easiest method for both older Excel versions and WPS Spreadsheets, utilizing standard logic to compare a running count to a total count.
By comparing a running count of the item to its total count in the entire column, you can instruct the spreadsheet to only display the count when the running total reaches the maximum count (which inherently occurs at the last row the item appears).
Click on the cell adjacent to your first data point (for example, B2 if your data starts in A2).
Type the formula: =IF(COUNTIF($A$2:A2, A2)=COUNTIF($A$2:$A$100, A2), COUNTIF($A$2:$A$100, A2), ""). Make sure to adjust the $A$100 to match the actual end of your data range.
Press Enter, then click the small square at the bottom-right corner of cell B2 and drag it down to fill the formula to the rest of the column.

Use Advanced Dynamic Array Formula (Microsoft 365)
A highly advanced, single-cell dynamic array solution using LET and SCAN, suitable for modern Excel environments where dragging formulas down is not desired.
Easily Count and Manage Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas, including nested IF and COUNTIF combinations, allowing you to easily track duplicates and format your data cleanly. Its highly intuitive interface ensures smooth handling of large datasets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your duplicate data.
- 2. Input the logic formula: Select the blank cell next to your first record and type: =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(A:A,A2),"").
- 3. Fill the series: Double-click the fill handle (the small green square at the bottom right of the cell) to automatically apply the formula down the entire column.
- 4. Review the clean output: Scroll through your list to verify that counts are now exclusively appearing next to the final instances of repeated values.

Frequently Asked Questions
Why is my COUNTIF formula showing the total count on every row?
If you only use =COUNTIF(A:A, A2), the spreadsheet evaluates the entire column for every row and outputs the total count each time. You must nest it inside an IF function that compares a running count to the total count, returning blank spaces for earlier occurrences.
Does my data need to be sorted for the COUNTIF solution to work?
No, the standard IF and COUNTIF combination formula evaluates the entire specified range dynamically. It will accurately find the absolute last occurrence of a value, even if the duplicates are scattered randomly throughout the dataset.
Can I highlight the last occurrence instead of printing a number?
Yes. You can use Conditional Formatting. Select your data range, go to Conditional Formatting > New Rule > Use a formula, and enter =COUNTIF($A$2:$A2,$A2)=COUNTIF($A$2:$A$100,$A2). Set your desired fill color to highlight those specific rows.




