Vlookup-ish type question

[JohnDouglas]

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?

thanks for your help

john

 

[Andrew Poulsom]

Try something like:

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

where Sheet2!A1:B10 contains your lookup table.

 

[JohnDouglas]

That works perfectly.

Many thanks

John

 

[shodan]

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.
thanks for your help.

 

[Andrew Poulsom]

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.
thanks for your help.

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.

 

[shodan]

thanks Andrew