Calculate Mileage total for a given day

[alarm_guy1]

Hi guys,
I have a Column A that contains the DAY i.e. mon tue wed thur fri
I have column B that contains
(A5)mon (B5)Travel to site = (mileage to site) (H5)mileage from (B5)
(A6)mon (B6)customer visited
(A7)mon (B7)lunch
(A8)mon (B8)Customer visited
(A9)mon (B9)Travel to home =(milage home) (H9)total mileage from (B5+B9)

(A11)tue (B11)Travel to Site = (Mileage to site) (H11) mileage from (B11)
(A12)tue (B12) customer
(A13)tue (B13)Travel to site =(mileage to site) (H13) mileage from (B13)
(A14)tue (B14) Customer
(A15)tue (B15)lunch
(A16)tue (B16)customer
(A17)tue (B17)travel to site =(mileage to site) (H17) mileage from (B17)
(A18)tue (B18)Customer
(A19)tue (B19)Travel to home =(mileage to site) (H19)total mileage from (B11+B13+B17+B19)

I will attach an example
Many thanks in advance cheers

[Kyle123]

What's the question?

[e.isayev]

ok....
Paste this Array formula to the F5 cell and drag down
=IF(ISNUMBER(FIND("Home from site",B5,1)),IFERROR(VALUE(RIGHT(B5,LEN(B5)-FIND("=",B5,1))),0)+SUM(($A4:A$5=A5)*IFERROR(VALUE(RIGHT($B4:B$5,LEN($B4:B$5)-FIND("=",$B4:B$5,1))),0)),IFERROR(VALUE(RIGHT(B5,LEN(B5)-FIND("=",B5,1))),0))

[Kyle123]

There's no need for an array:

=IFERROR(VALUE(RIGHT(B5,LEN(B5)-FIND("=",B5))),0)  

Then just sum them up?

[e.isayev]

What about the weekdays ( mon,tue.....). thats why i used array formula.

[alarm_guy1]

Hi guys,
Thanks for the super quick replies
The Array seems to give me a #Value! @ F13 for some reason (not that I pretend to understand what is going on)
The Non array only gives me the info in Column B for Start and End nothing for travel within that day and no total

regards

Keith

[e.isayev]

Hi,
I attached the file on my reply. The only thing I corrected in your table is merged cells. Have a look to the excel file,

[Kyle123]

What about the weekdays ( mon,tue.....). thats why i used array formula.

=IF(LEN(A5)>0,IFERROR(VALUE(RIGHT(B5,LEN(B5)-FIND("=",B5))),0),SUM(OFFSET($A$5,MATCH(A4,A:A,0)-5,7,COUNTIF(A:A,A4))))  

Although feel free to replace offset with index for non volatile

[alarm_guy1]

Thankyou for the last reply, and sorry to be a pain, but all that seems to do, is give me a total for B5 and B13, but it does not take into account still the random travel to sites during the day
regards
Keith

[alarm_guy1]

Hi Eisayew,

How weird, If I copy and paste your array I get an Error, however if I copy your Column from the Example into my real sheet it works a treat.

many thanks for thehelp

Cheers Keith

[Kyle123]

How so? I think you need to be clearer on what you actually want. Since I don't think you've actually asked a question?

[alarm_guy1]

Ok, upon further investigation, it appears your does work perfectly, however once i paste it into my working sheet it does not perform at all, It may be down to in my working sheet, as I see you have used A4 at the end of your formula, as my A4 is populated with other Info not related to this

Basically I dont get a total calculation

Thanks once again, I can post my original; working copy if required

[e.isayev]

Ok, upon further investigation, it appears your does work perfectly, however once i paste it into my working sheet it does not perform at all, It may be down to in my working sheet, as I see you have used A4 at the end of your formula, as my A4 is populated with other Info not related to this

Basically I dont get a total calculation

Thanks once again, I can post my original; working copy if required

You use merged cells in your table. But I deleted them and used only one cell. Merged cells cause a problem in my formula. Coz it gives different numbers of array numbers.

[alarm_guy1]

Ah, I have no choice but to use Merged cells as it is a predefinded form that I have to use and cannot alter the layout.
cheers

[Kyle123]

I think mine works with merged cells

[alarm_guy1]

Hi Kyle123,
Unfortunately your code does not function within my live environment either.

I have attached the real file if u are happy to take a look

Cheers

Keith

[Kyle123]

It's because you have 0 where there is no day instead of nothing, either delete these or use:

=IF(LEN(A5)>1,IFERROR(VALUE(RIGHT(B5,LEN(B5)-FIND("=",B5))),0),SUM(OFFSET($A$5,MATCH(A4,A:A,0)-5,7,COUNTIF(A:A,A4))))  

Notice the check for the length of the cell's contents is now 1, rather than 0

[alarm_guy1]

Hi kyle123, u are a genius, how you quickly you diagnosed that issue.

Cheers.

A bit of tweaking and then onto the next problem..

many thanks for you extreme patience

Keith

[Kyle123]

No problem, glad you got it working