Formula Help

[m0ws3r]

Hi,

I was wondering if anyone could help me solve a little problem I am currently experiencing. I am creating a load plan sheet for logistics purposes. Basically my data to look up against is a loading order onto a trailer, so fo eg

Loading Sequence
2
1
5
4
3
2
1

So every '1' is a trailer assigned, the countdown to one is drop order for the vehicle. So to he question, What I need is to create 'routes' so for eg


Loading Sequence
2
1 = route 1
5
4
3
2
1 = route 2

So the formula has to look down the data, and every time it finds a '1' it assigns a number, the first '1' would be 1, the second would be 2 and so on and so forth. The tricky part is for the forumla to look up the data it is returning to +1 to count up and return the ascending route number. Data below

Route	Delivery Sequence
0	2
1	1
0	2
1	1
0	4
0	3
0	2
1	1
2	1
0	5
0	4
0	3
0	2
1	1

<tbody>
</tbody><colgroup><col><col></colgroup>

Current Forumla =IF(C3=1,A2+1,0)

Any help would be greatly appreciated

Many Thanks Mick

 

[bbott]

Try this in A2 and copy down:

=IF(B2=1,COUNT($A$1:A1)+1,"")

 

[m0ws3r]

Try this in A2 and copy down:

=IF(B2=1,COUNT($A$1:A1)+1,"")

After entering the forumla, it says I have entered too few arguements due there being no criteria set for the countif, I modified the forumla to:

=IF(B2=1,COUNTIF($A1:A1,1)+1,"")

That works up until it detects the next value =1, and then stops counting up and returns the value of 2

 

[bbott]

After entering the forumla, it says I have entered too few arguements due there being no criteria set for the countif, I modified the forumla to:

=IF(B2=1,COUNTIF($A1:A1,1)+1,"")

That works up until it detects the next value =1, and then stops counting up and returns the value of 2

Did you copy the formula or type it in yourself? I used the COUNT function, not COUNTIF.

 

[m0ws3r]

Ah yes, my apologies, as i copied the formula over my excel had abit of a fit so i typed in and assumed the countif rather than reading the count.

Many thanks for your help, and now seems such an easy fix!

Thanks
Mick

 

[Scott Huish]

It could be done with COUNTIF but you have your range incorrect:

=IF(B2=1,COUNTIF($B$2:B2,1),"")

 

[bbott]

Ah yes, my apologies, as i copied the formula over my excel had abit of a fit so i typed in and assumed the countif rather than reading the count.

Many thanks for your help, and now seems such an easy fix!

Thanks
Mick

You're welcome.