# 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

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)

D

#### Don Guillett

another
=SUMPRODUCT(--(ISNUMBER(MATCH(J2:J4,A2:F2,0))*K2:K4))

M

#### Mal

Don,
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

M

#### Mal

Thanks Ron.
I believe you have nailed it.
All help appreciated.

Mal

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

M

#### Mal

Don,
I think you misread what I said. I said if you change c2 from 9 to 1 the
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.

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