Sum / Lookup

  • Thread starter Thread starter Mal
  • Start date Start date
M

Mal

I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0
Click to expand...

I want to look up each of the values in A to F, assign a value from column
K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal
 
One way
=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D2,J2:J4=E2,J2:J4=F2))*K2:K4)
 
Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal
 
Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.



Gord
 
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal
 
Apologies Mal.

Did not fully test..............left out row 4 data in J and K so was
not your described layout as I stated.

I see Ron has you set up.


Gord
 
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal
Click to expand...

I just re-tested and find that, using the second formula, if you
change c2 to 9 you still get 6 because your numbers still have
1,2,3......Try changing e2
 
Don,
I think you misread what I said. I said if you change c2 from 9 to 1 the
answer should be 9.
The data in A2 to F2 is variable so if for example each of the six cells (a2
to f2) had the figure "1" in them, we apply a value of "3" to each from the
table J2:K4) and sum 6 times 3 = 18.
If you put "1" into each of cells a2:f2 I think you will see the result
comes up as 3 when it should be 18. I can not see why this is so.
However I am happy as Ron as given me a solution.
Thanks for your help.

Mal
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the
result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal
Click to expand...

I just re-tested and find that, using the second formula, if you
change c2 to 9 you still get 6 because your numbers still have
1,2,3......Try changing e2
 
Back
Top