Running Count

[cameronbenson]

So i have looked everywhere to find a solution to this and nothing has helped thus far. I need a simple, minimal-work, solution and i want to avoid macros if possible. There are already some in the workbook and I dont want to make it too much bigger.

My scenario is this:

I have a data sheet that is used to record orders (start and finish time), user, and completion status. There is an event macro that inputs the DTTM into Column H when the Employee ID is entered into Column F. Column B takes the date from column H using an INT() function. Column B will be the same date for multiple rows of the data as the day goes on. My issue is with Column C.

My issue is this:
I need Column C to count the number of rows up to that row, with the criteria that the date is equals the date it's being entered. The reason for this and not a running numbering is because when they go to enter the data in on the following day, the numbering needs to reset.

For example...

1/2/16 1
1/2/16 2
1/2/16 3
1/2/16 4
1/2/16 5
1/2/16 6
1/2/16 7
1/2/16 8
1/2/16 9
1/2/16 10
1/2/16 11
1/2/16 12
1/2/16 13
1/3/16 1
1/3/16 2
1/3/16 3
1/3/16 4
1/3/16 5
etc.

i tried using a COUNTIF($B$4:B4,B4) so that the range would increase as new data was entered into the table, but i'm getting an error. Every time new data is entered into the table, the formula errors out saying "restore to calculated column" and the numbering messes up. I need help! I'd like to think I'm pretty good with Excel but i'm at the end of my ropes with this.

I'm using Windows 7 with Excel 2013.

 

[BR3XLSFAN]

I tried this in Excel 2013 (Win 10) with no issues.

Are you using a table or simply entering values into the worksheet? Calculated columns are a feature of tables and may be part of the problem?

If you can provide any other details, can look at it again...

 

[The Wizard]

If your date is in column B, you should be able to use a simple formula such as the following (starting in cell C2, and assuming that your data has column headers):

=if(B2=B1,C1+1,1)

Basically, this is checking the date in B2 to see if it is the same as the date in B1. If it is not, then the assumption is that this is the first time this date has appeared in your data and produces the number 1. If it is, then the formula looks at the number above it and adds 1. This will only work if your dates are always in chronological order and you always add new dates at the end. Let me know if this is not the case.

 

[cameronbenson]

If your date is in column B, you should be able to use a simple formula such as the following (starting in cell C2, and assuming that your data has column headers):

=if(B2=B1,C1+1,1)

Basically, this is checking the date in B2 to see if it is the same as the date in B1. If it is not, then the assumption is that this is the first time this date has appeared in your data and produces the number 1. If it is, then the formula looks at the number above it and adds 1. This will only work if your dates are always in chronological order and you always add new dates at the end. Let me know if this is not the case.

Yet another example of me overthinking things. Thank you so much for your quick reply. I apologize for not responding quicker, I have been dragged around on other projects lately and haven't had time to work on this one. I guess it goes to show you that the more you know, the more difficult simple solutions become. Again, thank you so much for helping me with this.