Sum / Lookup


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

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
 
Ad

Advertisements

D

Don Guillett

One way
=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D2,J2:J4=E2,J2:J4=F2))*K2:K4)
 
M

Mal

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
 
G

Gord Dibben

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
 
M

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
 
Ad

Advertisements

G

Gord Dibben

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
 
D

Don Guillett

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

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
 
Ad

Advertisements

M

Mal

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

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
 

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