Excel Formula's

[watkins6878]

Hi I have got a speedsheet to work out the MPG for 25 lorries that my company run but when we receive our fuel bill I want to write a formula that will lookup all lines of a certain vehicle reg then add all the amounts of fuel used by the lorry and total it up for me, the formula i am using so far looks for the vehicle reg but only returns the value of the first line it finds it on, Can anyone help (windows xp, excel 2003)

Thanks
Alan

 

[barry houdini]

Welcome to the board

If you have vehicle registrations in column A and fuel amounts for each in column B then for a specific vehicle reg in E1

=SUMIF(A:A,E1,B:B)

something like this....

Book1
	A	B	C	D	E	F
1	vehicle reg	fuel used		reg	abc123
2	abc123	1		total fuel	12
3	efg564	2
4	abc123	3
5	efg564	4
6	lfr231	5
7	pes453	6
8	abc324	7
9	abc123	8
10
Sheet1

 

[watkins6878]

If you have vehicle registrations in column A and fuel amounts for each in column B then for a specific vehicle reg in E1

=SUMIF(A:A,E1,B:B)

something like this....

Book1
	A	B	C	D	E	F
1	vehicle reg	fuel used		reg	abc123
2	abc123	1		total fuel	12
3	efg564	2
4	abc123	3
5	efg564	4
6	lfr231	5
7	pes453	6
8	abc324	7
9	abc123	8
10
Sheet1

Hi thanks for a quick reply to my Question

My fuel bill is in one speedsheet and the MPG is in another would they both have to be open for this formula to work or could i just add the name off the workbook to this formula

Hi I have got a speedsheet to work out the MPG for 25 lorries that my company run but when we receive our fuel bill I want to write a formula that will lookup all lines of a certain vehicle reg then add all the amounts of fuel used by the lorry and total it up for me, the formula i am using so far looks for the vehicle reg but only returns the value of the first line it finds it on, Can anyone help (windows xp, excel 2003)

Thanks
Alan

 

[barry houdini]

It should work fine even if the source workbook is closed, although it's easier to set up initially when they're both open because then you only need to reference the workbook name and sheet name rather than the complete path, something like

=SUMIF([Book1.xls]Sheet1!A:A,E1,[Book1.xls]Sheet1!B:B)

 

[watkins6878]

Ok Thank you very much

It should work fine even if the source workbook is closed, although it's easier to set up initially when they're both open because then you only need to reference the workbook name and sheet name rather than the complete path, something like

=SUMIF([Book1.xls]Sheet1!A:A,E1,[Book1.xls]Sheet1!B:B)

Alan

 

[Brian from Maui]

The target workbook must be open for SUMIF to work. If you insist on having a closed target workbook, you'll have to use a formula that operates on arrays, something with Sumproduct.


=SUMPRODUCT(--('[MPG.xls]201'!$A$2:$A$1000=E1),'[MPG.xls]201'!$C$2:$C$1000)

No whole column references.

 

[barry houdini]

Hey Brian!

You are, of course, correct.

My apologies for misleading the OP

 

[Brian from Maui]

Hey Brian!

You are, of course, correct.

My apologies for misleading the OP

Well, SUMIF does work if the target workbook is open!