Formula to count nth occurence of element in list

  • Thread starter Thread starter hglamy
  • Start date Start date
H

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
 
Hi
in A1 put the following formula
=COUNTIF(INDIRECT("$B$1:$B$" & ROW(B1)),B1)
copy down

HTH
Frank
 
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
 
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
 
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
 
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
 
Hi,

Assuming Data starts on second row,
in A2:

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

Regards,

Daniel M.
 
Back
Top