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
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
6))