Results 1 to 6 of 6

Thread: How to sum by using Lookup on Excel?

  1. #1
    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.

  2. #2
    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))

  3. #3
    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.

  4. #4
    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.

  5. #5
    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.

  6. #6
    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)))

Similar Threads

  1. Excel lookup query
    By reklev in forum Windows Software
    Replies: 1
    Last Post: 23-06-2012, 02:37 PM
  2. EXCEL Lookup the same value that has multiple values
    By horse43 in forum Windows Software
    Replies: 1
    Last Post: 09-06-2012, 11:12 AM
  3. LOOKUP.XLAM is already open in Excel 2007
    By Japheth in forum Windows Software
    Replies: 6
    Last Post: 27-05-2010, 05:29 PM
  4. Lookup multiple values in excel
    By Alatar1313 in forum Windows Software
    Replies: 1
    Last Post: 09-12-2009, 02:19 AM
  5. Cell intersection lookup in excel
    By Sayam in forum Windows Software
    Replies: 3
    Last Post: 30-07-2009, 10:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,638,457,458.17546 seconds with 17 queries