ExcelBanter - Using VLOOKUP to Sum values from multiple columns

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Using VLOOKUP to Sum values from multiple columns (https://www.excelbanter.com/new-users-excel/445492-using-vlookup-sum-values-multiple-columns.html)

Using VLOOKUP to Sum values from multiple columns

I am trying to use VLOOKUP to sum multiple values from different columns. I need this because the value is given by name of machine, and in the column directly right of that the cost is given. The table looks like this.
2010 2011
A B C D
M086-1 7000 M105 10000
M105 5000 F120 9200
F120 2500 M086-1 7600
F102 1500 F102 5500

It goes on like this for a list of around 100 machines, and there are about 8 columns.
So i need to sum the values for each machine over these columns.
Say for instance i want to get the total for M105 over these two years, i have been using:

=SUMPRODUCT(VLOOKUP("M105",$A$3:$D$6,{2,4},FALSE))

Then hitting ctrl+shift+enter

This gives me an incorrect value though...

EDIT:

Used SUM and SUMIF to specify machine and that is working much better than previous formula.

Quote:

Originally Posted by Askyou (Post 1599831)

Hi,

Easily done using just =SUMPRODUCT if you still need help.

Email me your current workbook or PM me if you need a contact email address and I'll happily show you how.

I need to see more of the data to make it actually work, so easier to see your workbook than give half a reply here.

S.

Using VLOOKUP to Sum values from multiple columns

No VLOOKUP()s or arrray formulae are needed:

=SUMPRODUCT(--(A3:A6="M105")*(B3:B6))+SUMPRODUCT(--(C3:C6="M105")*(D3:D6))