Printable View
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.
One way,
another way is,Code:=SUMPRODUCT((OR(J2:J4=A2,J2:J4=B2,J2:J4=C2,J2:J4=D 2,J2:J4=E2,J2:J4=F2))*K2:K4)
Code:=SUMPRODUCT(--(ISNUMBER(MATCH(J2:J4,A2:F2,0))*K2:K4))
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.
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.
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.
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:
Code:=SUMPRODUCT(N(OFFSET(K1,MATCH(A2:F2,J1:J4)-1,0)))