Results 1 to 2 of 2

Thread: Need your help guys! Lookup type formula needed.

  1. #1

    Need your help guys! Lookup type formula needed.



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

    Hi all,

    Hoping someone can help me find the right formula.

    I have a table with 2 columns. The 1st column has codes in it (i.e B8, B10, B12, etc), and each code can occur multiple times (i.e B8 can be on row 10,40,60 & 80). The 2nd column has a numerical value. What I want to do is put a formula in a cell below the table which will calculate the sum of each numerical value corresponding to a particular code (i.e say code B8 occurs 25 times in the first row, I want a formula which instantly calculates the sum of all 25 corresponding numerical values in the 2nd column). So far the only way I can see to do it is to filter the table, then sum the numerical values manually. I tried to use vlookup, but this will only return the first value, so is no use. I'm sure there must be an IF function that works, but I can't find a way to express 'B8 is contained within a range (A5:A15)' in the logical test box.

    Any help would be greatly appreciated as this type of function will be extremely useful for my work!!

    Many thanks,

    Rob

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You can use SUMIF for that.

    e.g.

    =SUMIF(A1:A10,X1,B1:B10)

    This will sum values in B1:B10 where A1:A10 equals the value in X1.


Posting Permissions

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