How to sum by using Lookup on Excel?

[Gowravaram]

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.

[JustineBB]

One way,

=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D 2,J2:J4=E2,J2:J4=F2))*K2:K4)

another way is,

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

[Gowravaram]

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 check and let me know as soon as possible.Thanks a lot again.

[JustineBB]

Please explain what "doesn't work" means. Errors? Nothing? Not 6?Using either of formulas in G2 returns 6 for me using your described data layout.

[Gowravaram]

Both formulars return "6" but if you change the values in A2:F2, the resultis 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.

[BillieJ]

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