Function Problems

Use COUNTIF in Excel to Show Running Occurrences as 1 of 4

Amos GikundaAmos Gikunda Nov 12, 2026 870 views

Question details

Create a formula that displays each repeated value's running occurrence number and its total count, such as 1 of 4, 2 of 4, and 3 of 4.

Use COUNTIF in Excel to Show Running Occurrences as 1 of 4
Product
Microsoft Excel
Device & OS
not provided
Scenario
The values to count are in column C beginning in cell C2, and the result is needed beside each row.
Observed behavior
Each row should show how many times its value has appeared so far, followed by the total number of times that value appears in the column.
Before you start

Confirm that the first value in the list is in C2 and that the cells in column C contain consistent text or numbers. Enter the formula in a separate result column, such as D2, so the source values remain unchanged.

Solution 1Recommended

Combine a running COUNTIF with a total COUNTIF

Use this formula when you need a sequential label for every repeated value in column C, including the value's final total occurrence count.

The first COUNTIF uses an expanding range from C2 to the current row, so it returns the current occurrence number. The second COUNTIF checks the entire column and returns the total number of matching values.

1
Select the first result cell

Click the cell next to the first source value, such as D2 if the value to count is in C2.

2
Enter the occurrence formula

Type =COUNTIF($C$2:C2,C2)&" of "&COUNTIF(C:C,C2) and press Enter.

3
Fill the formula down

Select D2, then drag the fill handle at the lower-right corner down through the remaining rows in your list.

4
Check a repeated value

Find a value that occurs multiple times in column C. Its results should progress, for example, from 1 of 4 to 2 of 4 and then 3 of 4.

Combine a running COUNTIF with a total COUNTIF
Formula result: For a value that appears four times in column C, the four matching rows will display 1 of 4, 2 of 4, 3 of 4, and 4 of 4.
WPS Spreadsheets formula tool

Create Running COUNTIF Occurrence Labels in WPS Spreadsheets

WPS Spreadsheets supports the same COUNTIF formula structure for counting each value as it appears and showing its total frequency. Enter the formula once, then use AutoFill to apply it to the full result column.

  1. 1. Open the worksheet in WPS Spreadsheets: Open your workbook and locate the list of values in column C, starting at C2.
  2. 2. Choose the result column: Click D2 or another empty cell beside the first value where you want the occurrence label to appear.
  3. 3. Insert the COUNTIF formula: Enter =COUNTIF($C$2:C2,C2)&" of "&COUNTIF(C:C,C2), then press Enter.
  4. 4. Apply the formula to all rows: Click the formula cell and drag its fill handle down the result column to copy the formula.
Create sequential labels such as 1 of 4 for duplicate entries.Use COUNTIF formulas with familiar Excel-style syntax.Open and edit Microsoft Excel .xlsx and .xls workbooks.Use AutoFill to copy formulas quickly through long lists.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the first COUNTIF range use $C$2:C2 instead of C:C?

$C$2:C2 starts at a fixed first cell and expands as the formula is filled down. This lets COUNTIF return the first, second, third, and later occurrence for the current value.

Can I use this formula when my data starts in another column?

Yes. Replace every C reference with the column containing your values. For example, if values begin in A2, use =COUNTIF($A$2:A2,A2)&" of "&COUNTIF(A:A,A2).

How can I avoid counting blank cells?

Wrap the formula in an IF test: =IF(C2="","",COUNTIF($C$2:C2,C2)&" of "&COUNTIF(C:C,C2)). This keeps the result cell blank when C2 is blank.

Does COUNTIF treat uppercase and lowercase text as different values?

No. COUNTIF is not case-sensitive, so values such as "Apple" and "APPLE" are counted as the same text.