Using VLOOKUP to Sum values from multiple columns

A

Askyou

I am trying to use VLOOKUP to sum multiple values from differen
columns. I need this because the value is given by name of machine, an
in the column directly right of that the cost is given. The table look
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 over 100 machines, and there ar
about 10 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... One thing i have come acros
that works is by using this;

=SUM(VLOOKUP("FG-F102",$C$5:$D$92,{2},FALSE),VLOOKUP("FG-F102",$E$5:$F$92,{2},FALSE),VLOOKUP("FG-F102",$G$5:$H$92,{2},FALSE),VLOOKUP("F102",$A$5:$B$92,{2},FALSE))

I just used a different VLOOKUP for each year, and summed those values.
This is a bit inconvenient though, as i have to change more values t
get a different machine.

ANOTHER problem that i have, is that the machines aren't all listed th
same for every year.

For instance, one year the name may be F120, but another it is FG-F120
and some years it's not listed at all!

When it's not listed obviously i get a value error because VLOOKUP ca
not find the thing i am looking for.

Any help with streamlining this would be greatly appreciated
 
J

James Ravenswood

No VLOOKUP()s or arrray formulae are needed:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top