# Thread: How to sum by using Lookup on Excel?

1. Member
Join Date
Nov 2011
Posts
60

## How to sum by using Lookup on Excel?

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.  Reply With Quote

2. Member
Join Date
Jul 2011
Posts
434

## Re: How to sum by using Lookup on Excel?

One way,
Code:
`=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,
Code:
`=SUMPRODUCT(--(ISNUMBER(MATCH(J2:J4,A2:F2,0))*K2:K4))`  Reply With Quote

3. Member
Join Date
Nov 2011
Posts
60

## Re: How to sum by using Lookup on Excel?

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.  Reply With Quote

4. Member
Join Date
Jul 2011
Posts
434

## Re: How to sum by using Lookup on Excel?

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.  Reply With Quote

5. Member
Join Date
Nov 2011
Posts
60

## Re: How to sum by using Lookup on Excel?

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.  Reply With Quote

6. Member
Join Date
Jul 2011
Posts
440

## Re: How to sum by using Lookup on Excel?

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)))`  Reply With Quote

excel, microsoft office, microsoft, worksheet, lookup 