How to Keep a Running Count in Excel

To keep a running count in Excel, put your records in one column and enter =COUNTA($A$2:A2) beside the first record; fill the formula down to count entries cumulatively. The fixed starting reference and expanding ending reference make each row count all nonblank entries from the first row through that point. This guide uses Microsoft Excel for Microsoft 365 on Windows and explains how to handle blank rows and other common counting needs.

What You’ll Need

An Excel worksheet with one record per row in a source column, such as names in column A.
An empty column next to the records for the running count.
A header row is helpful but not required. The examples assume headers in row 1 and data starting in row 2.

Step-by-Step

Arrange the data in one column. Put each item you want to count on its own row, with no merged cells. For example, enter a person’s name, order number, or task in A2, A3, and downward. A running count is most reliable when each occupied cell represents one record. If your records have several fields, choose a column that is filled in for every valid record, such as an ID or name column.
Add a heading for the count. In B1, type a label such as Running Count. Keeping the result beside the source data makes it easier to check which record each count belongs to. If you already have a heading row, leave it in place and put the formula in the first data row, not in the heading.
Enter the formula beside the first record. With records in A2:A5, enter this in B2: =COUNTA($A$2:A2). If A2 contains the first record, the result in B2 is 1: there is one nonblank entry from A2 through A2. COUNTA counts cells that are not empty, including text and numbers. The dollar signs keep the beginning of the range anchored when the formula is copied; the final A2 is allowed to move as the formula goes down.
Check the formula in the next row. Copy the formula to B3 and confirm that its range expands to A2:A3. If A2 and A3 both contain records, B3 should show 2. In B5, the equivalent range is A2:A5; if all four cells contain records, the result is 4. This simple check catches a common mistake: locking both ends of the range, which would make every row show the same count.
Fill the formula down. Select B2, point to the small square at the lower-right corner of the selected cell, and drag the fill handle down alongside your records. You can also select B2 and the cells below it, then press Ctrl+D on Windows to fill down. On Mac, use Command+D; dragging the fill handle works there as well. Excel adjusts the ending reference in each copied formula while keeping the starting point at A2.
Choose how blank rows should appear. The basic formula continues to display the current count on a blank source row. That can be useful if you want a count on every row, but it may look like the blank row is a record. If A2:A5 contains records in A2, A3, and A5, with A4 blank, put this version in B2 and fill it down: =IF(A2="","",COUNTA($A$2:A2)). The result in B4 is blank; B5 returns 3 because three source cells contain entries. The IF test hides the displayed result for a blank source cell, while COUNTA still counts the populated records above it.
Use an Excel Table if the list will grow. Select a cell in your data and choose Insert > Table. Confirm the selected range and whether it includes headers, then select OK. Add the running-count formula in the first data cell of the new count column. Excel generally treats that formula as a calculated column and fills it through the table; when you add a new table row, the formula can carry forward automatically. Check the first few results, especially if the table contains blank rows or imported data.
Save and verify with a small example. Check that the first populated record shows 1, the next shows 2, and the count increases by one for each additional record. If your list has gaps, verify that the next populated row continues the count rather than counting the gap. Save the workbook after checking the results. When you later add records below a normal range, copy or fill the formula into the new rows; a Table can reduce that maintenance.

For numbers only: COUNTA counts text as well as numbers. If the source column should contain numeric values only and you want to count only numbers, use this alternative. With numeric entries in A2:A5, enter it in B2: =COUNT($A$2:A2). If A2:A5 contains 10, 12, a blank, and 15, the result in B5 is 3 because there are three numeric cells in A2:A5. Use this only when text entries should not count as records.

For a running count by category: If column A contains categories and you want to count each category as it appears, use a conditional count. With categories in A2:A5 and the result in B2, enter =IF(A2="","",COUNTIF($A$2:A2,A2)) and fill down. If A2:A5 contains Red, Blue, Red, Red, the results are 1, 1, 2, and 3: each row shows how many times its category has appeared so far. The formula leaves a blank result when the category cell is blank.

Common Mistakes

Using the wrong source column. If the chosen column has blanks for valid records, COUNTA will undercount. Use a field that is consistently filled in, or decide on another rule for what qualifies as a record.
Locking the entire range. =COUNTA($A$2:$A$2) does not expand as you fill down, so it can return the same result in every row. Lock the starting cell only, as in =COUNTA($A$2:A2).
Counting formatting instead of entries. A cell that looks blank may contain a space, an apostrophe, or a formula. COUNTA treats a space or a formula as a nonempty cell, even if the formula displays an empty string. Check the formula bar and remove unwanted content if it should not count.
Including the heading in the range. Starting the formula at A1 when A1 contains a heading can make the first result 2 rather than 1. Match the formula’s starting row to the first data row.
Expecting COUNTA to count only numbers. Names, codes, and other text count too. Use COUNT when only numeric cells qualify, or choose a category-based rule when only particular records should count.

Tips

Keep the source data and running count in separate columns. This makes it clear what is being counted and avoids overwriting the original records.
Sort the entire dataset, not just the source column. Sorting one column alone can detach counts from their corresponding records. If you sort a range, include the count column; the row formulas will then recalculate for the new order.
If you filter a list, the running count still reflects the source rows in their worksheet order, including rows hidden by the filter. A basic COUNTA formula does not mean “count only visible rows.” If you need a visible-rows-only tally, that is a different calculation and should be set up specifically for that purpose.
For a frequently updated list, an Excel Table is usually easier to maintain than guessing how many formula rows to fill. Give columns clear headings and avoid blank rows inside the table.
Use a distinct number format or light fill for the count column if readers need to distinguish calculated results from entered data. Formatting does not change what COUNTA includes.
If the count is meant to number records rather than count a specific field, consider whether blank records should be excluded, as in the IF version. Write down that rule so another person using the workbook understands why some count cells are empty.

Troubleshooting

Every row shows 1. Inspect the formula in the second result row. Its ending reference should have moved down one row, such as A2:A3. If it still ends at A2, refill the formula from the first row and check that only the starting cell is fixed.

The first result is 2 or higher. Look for a heading, a space, or another value included in the starting range. Make sure the formula begins at the first data row. If the first data cell itself contains an unintended space, clear the cell and enter the record again.

The count skips a record. Confirm that the record is in the source column used by the formula and is not outside the filled range. If the cell appears populated but is not counted, check whether it is truly empty or whether a different formula or column is involved. COUNTA counts text and numbers, but it cannot count a record stored in another column when the formula only checks column A.

The count includes something that looks blank. Select the cell and check the formula bar. A space or a formula returning an empty string can make a cell appear blank but still be treated as nonempty by COUNTA. Delete accidental spaces. If the source cells contain formulas that return empty strings, test the counting rule on those cells rather than assuming COUNTA will ignore them.

New rows have no running count. If you are using a normal range, copy the formula into the additional rows or select the formula cell and use the fill handle. If you are using a Table, check that the new row is inside the table and that the count column has not been replaced with typed values. In Excel for the web, you can also use the fill handle; ribbon labels and shortcut behavior may vary slightly by browser and device.

Frequently Asked Questions

How do I make a running count that starts at zero?
For records in A2:A5, the basic formula starts at 1 for the first nonblank record. If you need a starting offset, you can add a number to the count, but first decide whether the first record should show zero or whether you mean an initial balance plus the records. For ordinary record numbering, starting at 1 is clearer.

Can I keep counting when there are blank rows?
Yes. COUNTA ignores genuinely empty cells in the range, so later records continue from the number of nonblank entries. Use the IF version if you also want the result cell beside each blank row to remain empty.

Does the count update when I add another record?
It updates when the new row is included in a formula’s expanding range. A Table can extend its calculated column as you add rows. With a regular range, fill or copy the formula down to the new row and verify the range.

Can I count repeated names or categories as they appear?
Yes. Use the COUNTIF example above to count how many times the current category has appeared from the first data row through the current row. It gives a separate running tally for each distinct category.

Why does my running count not change after I edit a cell?
Check that Excel calculation is set to Automatic on the Formulas tab under Calculation Options. Also confirm that the source cell is within the formula’s range and that the formula has not been replaced by a typed number. Press F9 on Windows to recalculate if needed; on Mac, use Fn+F9 on keyboards where the function keys have media controls.

Will the count include filtered-out rows?
Yes. COUNTA evaluates the range whether or not some rows are hidden by a filter. The values remain part of the running count; filtering changes what is displayed, not the formula’s range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.