Formula for running total (static count) of repeated values in a table

Originally Posted by HeyInKy

[...]when I go to add the next row, row 3, Excel automatically changed the formula in E2 to =COUNTIF($D$2:D3,D2) and my count was incorrect.

This should not be happening. How are you adding the next row, row 3? Are you simply entering more data in the next row, or are you inserting a row? Adding a row, even inserting a row, after row 2 should not affect the formula in row 2.

I suggest you attach your actual file, and explain how you are adding new data (just entering data, or inserting rows?). You also mention deleting rows; explain the scenario where you would delete a row so we can reproduce it.

By any chance... do you have calculation options set to manual, instead of automatic???

The countif in F is looking at column D. If you add something in column E and add nothing in column D, then the result of the countif WILL NOT CHANGE, until you add a value in column D... at which point, it works perfectly.

I do not see the point in referring the countif to column D, since the data validation in that column requires it to be a previously unused number, so the results of the countif will always be 1....

The DV Message reads:

The Interview ID must be 7 numeric digits and cannot be one previously entered.

But there is, admittedly, nothing in the formula to prevent duplication.

However, I still do not understand why you expect the formula:

=COUNTIF(D$2:D2,D2)

to change the result produced when you add a value to E and add NOTHING to D!!! Please explain....

I think I have (finally) been able to reproduce your problem. First check the formulas in F. They're fine. Select the last two franlins and drag down two rows. The formulae at teh foot of the table are all messed up. They are not in an orderly sequence.

I have seen this before... but cannot remember..

Try this in F2:

=COUNTIF(INDEX([Interview ID],1):[@[Interview ID]],[@[Interview ID]])

It seems to be OK... but this one has done my head in....

Formula for E2

Formula:

Please Login or Register  to view this content.

That doesn't work, if he's going to drag Franklin down....

see yellow cells.

You're welcome. It did have me baffled, for quite a while....

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.