# Vlookup-ish type question

#### JohnDouglas

##### Board Regular
Hi guys

ok, thanks giving today but hopefully there's still enough people around to help with this

I have the following data in two columns.

apples 2
bananas 5
total cost ????

then in another sheet i have the vlookup up table

apples £300
bananas £400

any ideas what forumla i could put in the "total cost ?????" that would sum the total cost of purchasing all the apples and bananas?

john

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try something like:

=SUMPRODUCT(B1:B2,SUMIF(Sheet2!A1:A10,A1:A2,Sheet2!B1:B10))

where Sheet2!A1:B10 contains your lookup table.

That works perfectly.

Many thanks

John

Andrew, this is interesting.

The fact that you use the sumif within the sumproduct is because the list might not be sorted and so give wrong totals. In a sorted list, the sumif isn't needed right?
Than secondly, when trying to evaluate the formula, the sumif indeed evaluates to the numbers by which must be multiplied, but when I enter the sumif the sameway, but as a standalone formula, it gives zero as result. Can you please explain this.
I'm really trying to understand the sumproduct well, because it since I've learned about it on this board, I find it amazing what this formula makes possible to do.

shodan said:
Andrew, this is interesting.

The fact that you use the sumif within the sumproduct is because the list might not be sorted and so give wrong totals. In a sorted list, the sumif isn't needed right?
Than secondly, when trying to evaluate the formula, the sumif indeed evaluates to the numbers by which must be multiplied, but when I enter the sumif the sameway, but as a standalone formula, it gives zero as result. Can you please explain this.
I'm really trying to understand the sumproduct well, because it since I've learned about it on this board, I find it amazing what this formula makes possible to do.

Given the OP's example the formula could indeed have been:

=SUMPRODUCT(B1:B2,Sheet2!B1:B2)

But if I had posted that, I was sure he would reply saying that he had more items in his lookup table than he posted. So I anticipated that.

If you enter:

=SUMIF(Sheet2!A1:A10,A1:A2,Sheet2!B1:B10)

in a cell you will only get the first result of the array (300).

If you enter:

=SUM(SUMIF(Sheet2!A1:A10,A1:A2,Sheet2!B1:B10))

and press Ctrl+Shift+Enter so that it is an array formula, you will get the sum of the array (300+400=700).

SUMPRODUCT is expecting arrays as its arguments, so the SUMIF returns {300;400} and this is multiplied by {2;5} to return 2,600.

thanks Andrew

Replies
4
Views
152
Replies
35
Views
937
Replies
3
Views
294
Replies
2
Views
137
Replies
3
Views
521

1,196,389
Messages
6,014,999
Members
441,864
Latest member
sweezy40

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