Uber Routes

[MARKEGANDERSON]

Hello Guys,

I am trying to come up with a formula for each Uber Routes.

For Example,

1. Route A to B = 4.5 miles
2. Route B to A = 4.5 miles
3. Route A to C = 10 Miles
4. Point C to A = 10 miles

So basically you have 10 different Points(A-J)

A4 = Pick up, C4 = Drop off
B4 = Mileage

How would I approach this?

I was thinking nested Ifs(but that's a lot of variables)

Any ideas?

 

[alz]

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Monday					Location		TO	From	Miles
3	Pickup	Miles		Drop Off:		A		A	B	4.5
4	C	5.5		B		B		B	A	4.5
5	Driver Name			Occupants		C		A	C	10
6						D		A	C	10
7								B	C	5.5
8								C	B	5.5
9								A	D	11
10								D	A	11
11								B	D	6.5
12								B	B	6.5
13								C	D	1
14								D	C	1
15

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(($H$3:$H$14=A4)*($I$3:$I$14=$D$4)*($J$3:$J$14))

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=$F$3:$F$6
D4	List	=$F$3:$F$6

Hi Try This!

 

[MARKEGANDERSON]

Thanks again for your help. B4 ==SUMPRODUCT(($H$2:$H$5=A4)*($I$2:$I$5=$D$4)*($J$2:$J$5)) is resulting in a zero.
I must be missing something?

 

[alz]

Value from A4 must be from the range of H2:H5
Value from D4 must be from the range of I2:I5

To avoid miss spelling Use Data Validation
In A4 =$H$2:$H$5
In D4 =$I$2:$I$5

 

[MARKEGANDERSON]

Value from A4 must be from the range of H2:H5
Value from D4 must be from the range of I2:I5

To avoid miss spelling Use Data Validation
In A4 =$H$2:$H$5
In D4 =$I$2:$I$5

Yes sir, not sure what I am missing

 

[alz]

Can you upload your sheet using XL2BB here?