# Formula Help

#### m0ws3r

##### New Member
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

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

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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Try this in A2 and copy down:

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

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

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.

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

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

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

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.

Replies
4
Views
316
Replies
0
Views
175
Replies
16
Views
647
Replies
8
Views
241
Replies
2
Views
194

1,221,239
Messages
6,158,717
Members
451,512
Latest member
aimarhu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back