Consecutive numbers using Row()

Question

Hi,

I am using the following formula to create a list of consecutive finishing positions to an event.

=IF(OR(C2=0,D2=0,E2=0),"---",ROW()-1). This formula is copied down from A2:A21.

All works well as long as none of the cells above =0.

For example if C2=0 & C3=0 then I have 2 rows of "---" above my 1st place person.

I thought I could use COUNTIF(A2:A21,"---") which equals 2 and minus this from the ROW() command however it doesn't work and gives me a circular reference error.

Can anyone help me with a formula to do this?

Thanks

Peter

Answer 1

all sorted now. I flag the non completion of the event in another sheet then use the Sequence formula.

Answer 2

Thanks Bernie.

That gives me a full list of 1-20 including the 2 rows I don't want included as they haven't finished the event which is why i give them the "---"

.

I want no.1 to start after the 2 rows of "---" above. If everyone completes the event then its easy however there maybe more than 2 rows on some events which is why I need to count the "---" in column A then somehow calculate how to start with number 1,2,3 etc after these rows.

Hope that makes sense.

Regards

Peter

Answer 3

How about this in A2, copied down:

=IF(C2="","",COUNTA($C$2:C2))