Results 1 to 4 of 4

Thread: Search a data set and return adjacent cell values and sum them within one cell

  1. #1
    Neophyte JonnyBravo's Avatar
    Join Date
    Sep 2018
    Posts
    2
    Articles
    0
    Excel Version
    1808

    Question Search a data set and return adjacent cell values and sum them within one cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I have two tables below which form a much bigger data set, I am trying to create a search of teh first table Col A, 52,53,54 etc. and return and sum all found instances in Col B. As you can see I have set up a search and it returns only the first instance it finds (i.e. 54 returns 19.40) it should return 38.8. I created a help colum to round down the original data set so i could match the data more easily. I am pretty stumped at the moment and hoping someone can help identify a better way to do this.

    If you need any more info let me know, I am continuing to work on it and will post a solution if i find one.

    Col B formula in first table:
    =SUMPRODUCT(VLOOKUP(A3,$B$8:$C$13,2,0))

    Col A Col B
    52 #N/A
    53 #N/A
    54 19.40
    55 #N/A
    69 41.50
    Col A Col B Col C
    54.9788 54 19.40
    54.9982 54 19.40
    69.9443 69 41.50
    69.9858 69 41.50
    70.7327 70 16.80
    70.7495 70 16.80

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,656
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps =SUMPRODUCT(($B$8:$B$13=$A3)*$C$8:$C$13)
    Thank you Ken for this secure forum.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,551
    Articles
    0
    Excel Version
    365
    or:
    =SUMIF($B$8:$B$13,A3,$C$8:$C$13)

  4. #4
    Neophyte JonnyBravo's Avatar
    Join Date
    Sep 2018
    Posts
    2
    Articles
    0
    Excel Version
    1808

    Thanks

    Thanks, sumif has done the trick!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •