# Excel Formula's

#### watkins6878

##### Board Regular
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
ABCDEF
1vehicle regfuel usedregabc123
2abc1231total fuel12
3efg5642
4abc1233
5efg5644
6lfr2315
7pes4536
8abc3247
9abc1238
10
Sheet1

barry houdini said:
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
ABCDEF
1vehicle regfuel usedregabc123
2abc1231total fuel12
3efg5642
4abc1233
5efg5644
6lfr2315
7pes4536
8abc3247
9abc1238
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

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)

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

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.

Hey Brian!

You are, of course, correct.

My apologies for misleading the OP

barry houdini said:
Hey Brian!

You are, of course, correct.

My apologies for misleading the OP

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

Replies
0
Views
264
Replies
1
Views
196
Replies
3
Views
269
Replies
19
Views
2K
Replies
1
Views
2K

1,203,632
Messages
6,056,452
Members
444,866
Latest member
cr130

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back