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

On Tue, 13 Dec 2011 10:52:12 +1000, "Mal"
wrote:

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




"Gord Dibben" wrote in message
.. .
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

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

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



"Mal" wrote in message
pond.com...
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

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