0

I have an excel spreadsheet containing a list of names and "scores" that they have received for a specific date. Each name should have 3 scores each month and I'm looking for a formula that can help keep track of this. So for example, if John Smith has been entered 3 times in the file there will be a 1,2, or a 3 next to the instance so that I know he's reached his final score for the month. The only formula I've been able to find is to count the overall number of instances in a range - but it would just put "3" in each cell instead of which order they occured in.

Also, there are about 200 different names in the file. If there is a way to drag the formula down and have it link to the cell directly that would be better so that I don't have to type 200 individual names into the formula each time.

Can anyone give me some advice?

1
  • If you are counting by month does that mean you want a formula that takes into account the dates......or do you have self-contained data for each month? Aug 30, 2014 at 11:40

1 Answer 1

2

It may work to keep using the count if, but instead of keeping the range the same make the range be from the first row of data until the row you are in.

A1--john S -- =countif(A1:A1,A1) -- =1

A2--Betty G -- =countif(A1:A2,A2) -- =1

A3--john S -- =countif(A1:A3,A3) -- =2

A4--Mike Z -- =countif(A1:A4,A4) -- =1

A5--John S -- =countif(A1:A5,A5) -- =3

A6--Terry T -- =countif(A1:A6,A6) -- =1

2
  • 1
    this works well and you can make this draggable by changing the first formula to =countif($A$1:A1,A1) and drag it down
    – datatoo
    Aug 29, 2014 at 20:52
  • Good point, it was odd writing a countif formula, I always use countifs as standard in case I ever want to add in any extra conditions later. Aug 31, 2014 at 18:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .