0

I have a dataset that looks like the following:

Employee Id|Date|Hours|Points
-----------------------------

There's several hundred employees and each has dozens of rows with different dates (and associated hours and points).

I need to write something that will assign a running count of days worked for each employee. It would start at 1 on their first day (based on the earliest date they worked) and then continue until there are no more days they worked. I also need to create a running total of their points.

Essentially I need it to look like this:

EmployeeId|Date|Hours|Points|Cumulative_days_worked|Cumulative_points_total
---------------------------------------------------------------------------

I don't even know where to start. Could anyone point me in the right direction?

Thanks

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
Shane Newman
  • 11
  • 1
    It's easier to help if you provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input data and the desired output. You really should choose either excel or R since you can only accept at most one answer. I'm really surprised that google searches turned up nothing. Have you really not tried anything thus far that we can help you with? – MrFlick Sep 21 '17 at 15:42
  • look at `cumsum` and `group_by` – BENY Sep 21 '17 at 15:44

1 Answers1

2

As simple formula, you could use:

=COUNTIF(A$1:A2,A2)

For E2 (and copy down) to get the running number for each ID.

=SUMIF(A$1:A2,A2,D$1:D2)

Would be F2 (and copy down)

enter image description here

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31