Forum Discussion

Janett1180's avatar
Janett1180
Copper Contributor
Jul 02, 2023

Have excel automatically count and add number in cell

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?
Formulas and Functions
Janett1180's avatar
Janett1180
Copper Contributor
Jul 03, 2023
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
SnowMan55's avatar
SnowMan55
Bronze Contributor
Jul 03, 2023

Janett1180 

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:

=COUNT($A$385:$A$15384)
…if column A contains numbers, such as your client number
=COUNTA($A$385:$A$15384)
…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.

 

Resources