Sum Rows in Lookup

[MargaretS]

My first post. I have two tabs, Assets and Fuel. On the Asset tab I want to total the miles for my three vehicles, Jeep 1, Jeep 2 and Car 1. There are multiple columns on both tabs in addition to the ones I listed; I included only the bare necessities in this example.

I have tried vlookup and sumif. I am able to pull the first row that matches (i.e. Jeep 1 returns 42 rather than 117 for miles). No matter what I do, I can't get 117 for the answer, let alone the correct figure for Jeep 2 and Car 1.

Running Excel 2016.

Thank you!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Table 1-Asset[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2-Fuel[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vehicle[/TD]
[TD]Miles[/TD]
[TD][/TD]
[TD]Vehicle[/TD]
[TD]Miles[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Jeep 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jeep 1[/TD]
[TD]42[/TD]
[TD]17.45[/TD]
[/TR]
[TR]
[TD]Jeep 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]10[/TD]
[TD]12.01[/TD]
[/TR]
[TR]
[TD]Car 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jeep 1[/TD]
[TD]75[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jeep 2[/TD]
[TD]65[/TD]
[TD]14.95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]14[/TD]
[TD]12.70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jeep 2[/TD]
[TD]64[/TD]
[TD]42.64[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]49[/TD]
[TD]32.75[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]13[/TD]
[TD]6.42[/TD]
[/TR]
</tbody>[/TABLE]

 

[cooper645]

SUMPRODUCT will do as you require

I made two sheets, named Asset and Fuel

Then added the headings Veh & Miles in Asset sheet
Then added the headings Veh, Miles and cost in fuel sheet

On the asset sheet in cell B2 I entered the above formula and just dragged it down.

It only works for the sample data you provided but you could edit it to ay size array or location

Coops

=SUMPRODUCT((Fuel!$A$2:$A$9=Asset!A2)*Fuel!$B$2:$B$9)

 

[Michael M]

Why not just Insert a PivotTable ??

Excel 2007
	H	I	J	K
1
2		Values
3	Row Labels	Sum of Miles	Sum of Cost
4	Car 1	86	63.88
5	Jeep 1	117	42.45
6	Jeep 2	129	57.59
7	Grand Total	332	163.92
8
9
10
Sheet1

 

[MargaretS]

SUMPRODUCT will do as you require

I made two sheets, named Asset and Fuel

Then added the headings Veh & Miles in Asset sheet
Then added the headings Veh, Miles and cost in fuel sheet

On the asset sheet in cell B2 I entered the above formula and just dragged it down.

It only works for the sample data you provided but you could edit it to ay size array or location

Coops

=SUMPRODUCT((Fuel!$A$2:$A$9=Asset!A2)*Fuel!$B$2:$B$9)

That did the trick. Thank you.

 

[MargaretS]

Hi Michael - I didn't want a pivot table in this worksheet, just pulling data from another tab. I wanted the summed miles by vehicle included so I could produce a pivot after further analysis.

Thank you for your assistance.

 

[MargaretS]

I appreciate your help; I should have reached out earlier

 

[cooper645]

No problem, I'll keep subscribed for a while incase there are any issues.