Formula to count nth occurence of element in list

[hglamy]

Hello,

in a simple list of test of subjects and marks like:

A | B | C |
# | SUBJECT | MARK |
-------------------------------
| French | 5 |
| Maths | 6 |
| Physics | 2 |
| French | 3 |
etc.

I need a formula for the first column (#), that
produces the nth occurrence of a subject in the list,
so that it shows:

A | B | C |
# | SUBJECT | MARK |
-------------------------------
1 | French | 5 |
1 | Maths | 6 |
1 | Physics | 2 |
2 | French | 3 |
etc.

Who knows a formula for columns "A" that does that ?

Help is greatly appreciated.

Kind regards,

H.G. Lamy

 

[Frank Kabel]

Hi
in A1 put the following formula
=COUNTIF(INDIRECT("$B$1:$B$" & ROW(B1)),B1)
copy down

HTH
Frank

 

[hglamy]

Thank you Frank,

your solution, however, counts only the total number of occurrences of each
subject,
so that if Maths occurs 5 times in the list, I get a 5 in front of Maths
each times it occurs.

What I need is a formula that results in a 1 the first time "Maths" occurs,
a 2 the second
time Maths occurs, and so on.

Would you be able to adapt your suggestion slightly to accomplish that ?

Thank you very much in advance.

Kind regards,

H.G. Lamy

 

[Frank Kabel]

Hi,

this formula works for me. Maybe you can email me your sheet and i'll
have a look at it.
Could be a problem with absolute and relative references. If you copy
this formula down one row (in A2) it should read
=COUNTIF(INDIRECT("$B$1:$B$" & ROW(B2)),B2)

Frank

 

[Frank Kabel]

Hi

first: please do not attach files to this newsgroup. Send them via
private email. So i won't attach the Excel sheet. I send it to you at
your web.de address.

In your Excel sheet i entered the following formula in C5:
=COUNTIF(INDIRECT("$d$5:$d$" & ROW(D5)),D5)
copy down and voila

Frank

 

[Frank Kabel]

Hi
tried your email and got an error. So if you'd lieke to get your sheet,
send me an email (frank[dot]kabel[at]mummert[dot]de)

Frank

 

[Daniel.M]

Hi,

Assuming Data starts on second row,
in A2:

=COUNTIF($B$2:$B2,B2)

Regards,

Daniel M.

 

[hglamy]

Thank you all, that did it !

Regards,

H.G. Lamy