Problem defining a variable range to auto sum

[Excelisdrivingmemad]

Hi All,

I have been using excel for several years although only at a medium level but have now run across an issue which has me completely stumped. There may well be a very simple answer to this but if there is, then I haven't been able to find it yet despite spending several hours trawling though the forums and trying different methods.

I have attached a simplified example of the sheet and will now attempt to put into plain English what I am trying to do.

Cells A2 to D13 contain a data table which I cannot alter the layout of as it is imported from tables sent from another company. A1 to D1 contain the header row for this table, these headings are, Route number, Start point, End point and Distance.

In cell G2 the user enters a route number currently being selected using a simple data validation list.
In cell H2 they then select a start point dependent on which route has been selected. This is achieved using a data validation with the formula "=OFFSET(A1,MATCH($G$2,Route_number,0)-0,1,COUNTIF(Route_number,$G$2),1)"
In cell I2 they then select and end point, again dependent on the route selected earlier, using data validation with this "=OFFSET(A1,MATCH($G$2,Route_number,0)-0,2,COUNTIF(Route_number,$G$2),1)". This gives me one headache in that a less than intelligent user could select an end point prior to the start but I can deal with this via training so am not too worried about it.

My problem is with cell J2 which I want to look at the route, start point, and end point selcted, and then sum the distance travelled by the vehicle. This distance is cumulative so effectively just needs to sum the relevant cells in column D but I have hit the proverbial brick wall.

Sorry for the longwinded question, and I have attached an example of the sheet in the hope that someone will be able to tell me I'm a fool and it is easy, and, give me the answer...lol

Thanks for your time

Craig

[jeffreybrown]

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about a proper thread title and adjust accordingly...

[Excelisdrivingmemad]

Thanks Jeff, apologies.

[benishiryo]

hi there, welcome to the forum. not sure if i got you right. you could provide some egs like what does Route 2, Location 5, Location 7 should return? is it 0.21 + 0.07? if so, then maybe an array formula like this:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

[jeffreybrown]

NP...

Try...

=SUMPRODUCT(--(Route_number=G2),--(Start_Point=H2),--(End_Point=I2),D2:D13)

actually if this is on track with what you want, then for 2007+

=SUMIFS(D2:D13,Route_number,G2,Start_Point,H2,End_Point,I2)

[Excelisdrivingmemad]

What can I say other than thank you both. Ben's solution works for me exactly as I would need it to. appreciate the help from both of you and can't believe I got an answer so quickly when I have been stumped for hours.

Thanks Again

Craig