Sum / Lookup

[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

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

 

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

 

[Don Guillett]

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

 

[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

 

[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

 

[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

 

[Ron Rosenfeld]

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

Assuming that your A:F data is in Row 2; and that your J:K data starts in Row 1

Change J4 from >=4 to just the number 4

Then enter this formula in G2:

=SUMPRODUCT(N(OFFSET(K1,MATCH(A2:F2,J1:J4)-1,0)))

 

[Mal]

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

Mal

 

[Ron Rosenfeld]

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

Mal

Glad to help. Thanks for the feedback.

 

[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

 

[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

 

[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