Function Problems

How to Count Repeated Values and Display Count Beside Last Occurrence in Excel

Phi Hung VoPhi Hung Vo Nov 11, 2026 868 views

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.

How to Count Repeated Values and Display Count Beside Last Occurrence in Excel
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.
Before you start

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.

Solution 1Recommended

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).

1
Select the target cell

Click on the cell adjacent to your first data point (for example, B2 if your data starts in A2).

2
Enter the IF and COUNTIF formula

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.

3
Apply the formula to the column

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 Standard COUNTIF Formula (Compatible with All Versions)
Formula Breakdown: COUNTIF($A$2:A2, A2) calculates the running count up to the current row, while COUNTIF($A$2:$A$100, A2) gets the total count. The IF statement ensures the total is only printed when these two numbers match.
Effortless Data Analysis

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. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your duplicate data.
  2. 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. 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. 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.
100% compatible with Microsoft Excel (.xlsx) formats and formulas.Lightweight software architecture for fast processing of heavy data.Familiar UI reduces the learning curve for seamless migration.Free built-in data analysis tools and templates.
microsoft office alternative - wps office

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.