Have excel automatically count and add number in cell

Copper Contributor

I have a spreadsheet with various information on clients.  I need excel to automatically recount and enter the correct number when I remove a row.  What is the correct formula to enter?

3 Replies


"I need excel to automatically recount and enter the correct number when I remove a row."

A formula in a cell will (by default) be recalculated and the resulting value will be stored in that cell whenever the workbook is opened, and whenever Excel determines that a cell/range that the cell's formula depends upon has changed.  So the "enter the correct number" part is probably no problem.

Your use of the term "spreadsheet" suggests that these rows of client info are all on one worksheet. But what specifically do you want the recount to count?  A good way to decide: Which column is always populated for a (non-empty) data row? (If there are multiple such columns, choose one.)  Are the data values in this column all numbers, all textual information, or a mix?

If this data is in an Excel table, give us the table name and the column name.  If this data is not in an Excel table (i.e., it's just a rectangular range of data), give us the column letter and the first potential row number of data (essentially, how many rows to ignore because they contain page and/or column headers or just blank rows you want to keep).


Thank you for the reply. I'm referring to one worksheet. I will be comparing my data to another source that only provides a total count - the other source will remove a client name and I want to be easily determine which information has been removed. currently i am using a visual search. The names are in the same order by join date. I've assigned each client a number based on this information. When I remove a row, I want the client numbers to recount and the 2 sources to match the new total. The first cell I'm counting is A385


That's better, but I can't tell from your description whether column A contains your assigned client number or some other (possibly text) value. Here are formulas for the two possibilities:

…if column A contains numbers, such as your client number
…if column A contains text, or mixed text and numbers

Those assume that you have a maximum of 15,000 client rows. It's trivial to increase the end of the range if you feel you may have more rows.


But perhaps Excel could compare the rows for you.  Is the client number present in both sources? Or are the client names unique? Or is there another pair of data columns whose contents match between your two sources, aside from any deleted rows?


To demonstrate, let me suppose that column C in your first worksheet contains a unique client identifier that is also present in column D in your other source, which I will assume to be Other Sheet in the same workbook. (This method also works if the other source is in a different workbook, but it's a little messier.)  You can use a helper column in your first worksheet—I will use the column X—to hold an indicator of whether that client is matched on Other Sheet. See the attached workbook.