+ Reply to Thread
Results 1 to 6 of 6

Find match between ranges, then sum their associated cells in a different range

Hybrid View

  1. #1
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Find match between ranges, then sum their associated cells in a different range

    I am attaching a spreadsheet containing a simple example of what I want to do and the results I want. sum with criteria.xls
    In summary, I have 2 ranges.
    If the value in one range's cell matches a value in the other range, I want to add an associated cell from the first range to the related one in the other range and put that result in a new range.
    The formula I've tried is:
    =IF(Range2=A11, Twenty10b+B11, B11)
    My syntax is bad, among other things, I'm sure. Need help with this. Thanks.
    burnettec

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Find match between ranges, then sum their associated cells in a different range

    something like
    mind the trim you have leading space in column A

    Formula: copy to clipboard
    =IFERROR(VLOOKUP(TRIM(A3),$H$3:$I$14,2,FALSE)+B3,B3)


    or

    Formula: copy to clipboard
    =IFERROR(SUMPRODUCT(($H$3:$H$14=TRIM(A3))*(Twenty10b))+B3,B3)
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Find match between ranges, then sum their associated cells in a different range

    Thanks very much vlady, but could you tell me what the formula should look like without the TRIM?
    The spreadsheet I will apply this formula to has already been "trimmed" with a macro and reformatted before anything will be done to it.
    Thanks for your time if you have the patience. The Trim portion of this formula confuses me regarding the other functions I'm trying to figure out.

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Find match between ranges, then sum their associated cells in a different range

    the trim "deletes/removes" spaces before/after a value. since some of your example values has spaces before them I used the trim...
    If you've apply trim with macro in your original no need to put it.

    A1 --> space123
    trim(A1) will remove space

    vlookup becomes without trim
    Formula: copy to clipboard
    =IFERROR(VLOOKUP(A3,$H$3:$I$14,2,FALSE)+B3,B3)

    same with the sumproduct
    Formula: copy to clipboard
    =IFERROR(SUMPRODUCT(($H$3:$H$14=A3)*(Twenty10b))+B3,B3)

  5. #5
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Find match between ranges, then sum their associated cells in a different range

    Great! I understand now. 'Trim-range' can replace 'range' as the function component. Good to know.
    Last question: do I still need IFERROR without the trim, or can it just be IF?
    Thank you! As it's getting late here, I will test your formula on my "real" spreadsheet tomorrow and report back to conclude this thread.
    I appreciate your time on this.

  6. #6
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Find match between ranges, then sum their associated cells in a different range

    Vlookup formula worked great with a little editing of my source column to keep the vlookup range to the left of the range values to be summed for both result columns needed. I'm very excited! Will test SUMPRODUCT tomorrow and investigate IFERROR reasoning, too. Appreciate you...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1