Use COUNTIF in Excel to Show Running Occurrences as 1 of 4
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.

- 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.
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.
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.
Click the cell next to the first source value, such as D2 if the value to count is in C2.
Type =COUNTIF($C$2:C2,C2)&" of "&COUNTIF(C:C,C2) and press Enter.
Select D2, then drag the fill handle at the lower-right corner down through the remaining rows in your list.
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.

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. Open the worksheet in WPS Spreadsheets: Open your workbook and locate the list of values in column C, starting at C2.
- 2. Choose the result column: Click D2 or another empty cell beside the first value where you want the occurrence label to appear.
- 3. Insert the COUNTIF formula: Enter =COUNTIF($C$2:C2,C2)&" of "&COUNTIF(C:C,C2), then press Enter.
- 4. Apply the formula to all rows: Click the formula cell and drag its fill handle down the result column to copy the formula.

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.




