Dear all,
Hope to find a solution to this problem.
I have a long route with points A to F along its entirety.
The distance from A to B is 41, B to C is 11, so and and so forth.
The issue:
I am trying to use Index + Match + Sum to find the total distance between points B and E.
I am also trying to make the formula flexible so that the points can be changed to find distances between different points easily.
A B 41
B C 11
C D 127
D E 21
E F 2
Would really appreciate help on this issue. Thanks!
Try Point1 in F1, point2 in F2
Distance is:
Formula:
Please Login or Register to view this content.
See attached workbook
Thanks Geoff! Will give it a go.
I was trying to manipulate the formula, but I think because it uses the ROW formula I cannot move the formula around.
Is there a way around this?
Try this...
Start End Dist A B Start B B C End F C D Dist D E E F
F4=SUM(OFFSET(C1,MATCH(F2,$A$2:$A$6,0),0,MATCH(F3,B2:B6,0)-MATCH(F2,$B$2:$B$6,0),1))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this.
Start=E2
End=F2
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
You're spoiled for choice for solutions! If you're still interested in mine then read on.I was trying to manipulate the formula, but I think because it uses the ROW formula I cannot move the formula around.
Is there a way around this?
I don't think it's row() that was the problem. I tried cutting A1:F5 and pasting elsewhere and that did result in a #N/A error. The problem was a typo at the very end of the formula where $B$1:$B$6 should have been $B$1:$B$5. So the corrected formula is:
=SUMPRODUCT($C$1:$C$5,--(ROW($1:$5)>=MATCH($F$1,$A$1:$A$5)), --(ROW($1:$5)<=MATCH($F$2, $B$1:$B$5)))
With this change I was able to cut/paste A1:F5 elsewhere without problems.
If you're still interested in this solution and still have problems then let me know.
Last edited by GeoffW283; 01-14-2019 at 12:49 PM.
On further testing, if I select, say, A4:C4 then "Insert" to add an additional point then my formula breaks because of the row() function (although it's OK if you insert an entire row).
Neither Ford's solution nor kvsrinivasamurthy's have this weakness so you will be better off choosing one of theirs.
And another. A variation on Ford's.
Formula:Please Login or Register to view this content.
Dave
Thanks everyone for all your solutions. Big thanks to Geoff for always coming back to check and help
You are welcome. Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Happy to help
And same from me - thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks