Sum a range based on information from two different columns

[new.vbacoder]

I have found a lot of posts that are close, but not quite what I'm looking for (I think).

What I have is a from and to path in columns A and B, and a corresponding distance in C.

I am giving the user the option to pick Points along the path and I want it to report the corresponding distance (i.e. from A to F should be around 5,200).

Attached is the file. I think I need and index and a sumif or sumproduct, but I can't quite figure it out.

Thanks in advance.

J

[DonkeyOte]

Pre XL2007 2 SUMIFs should work

Please Login or Register  to view this content.

(or use SUMPRODUCT but I'd opt for above myself)

XL2007+ use SUMIFS.

Please Login or Register  to view this content.

[TMS]

One way:

Cell G2: =SUM(INDIRECT("C"& MATCH(E2,$A$2:$A$11,0)+1 & ":C" & MATCH(F2,$B$2:$B$11,0)+1))

Regards

[new.vbacoder]

One way:

Cell G2: =SUM(INDIRECT("C"& MATCH(E2,$A$2:$A$11,0)+1 & ":C" & MATCH(F2,$B$2:$B$11,0)+1))

Regards

That works when the data is on the same sheet, but when the reference is in a different workbook (i.e. the data in column A-C is in A.xls and the data in columns E-G is in B.xls).

What do I need to modify the "C" and ":C" to? I'm assuming that's the column reference for the distance?

Thanks

J

[TMS]

That would be:

Cell G2 in Book B, referring to Book A:

Please Login or Register  to view this content.

Regards

[DonkeyOte]

FWIW, none of the suggestions made thus far will work should the target workbook be closed - should that be the case then use SUMPRODUCT (or Array) equiv. of the SUMIFS approach illustrated previously.

Even if the target is always open when the calculation workbook is open I would be very wary of having Volatile links (ie INDIRECT).

[new.vbacoder]

Pre XL2007 2 SUMIFs should work

Please Login or Register  to view this content.

My example was very generic (i.e. A to B, B to C, etc), and your code works great for that, but the real data has differing names, such as Winding Way or Burnside, and when using the above formula, the first row becomes negative and not even the correct value, and the second comes in positive, but not the correct value. I've also included the INDIRECT formula, as that seems to work either way. However, when trying to do INDIRECT over different spreadsheets, I can't get the formulas to work.

Here's the code for my specific example

Please Login or Register  to view this content.

Where Column B is the same as Column A in test, and Column D is the same as Column C.

I've re-attached test with the names included, as well as the basic letters.

Any thoughts??

J

[DonkeyOte]

Perhaps try:

Please Login or Register  to view this content.

In the above it is assumed that in [Travel Time.xls] From is B, To is C and Distance is D.

[new.vbacoder]

Message deleted by user.