+ Reply to Thread
Results 1 to 11 of 11

What formula to use for matches in column and return the sum of all match values in adjace

  1. #1
    Forum Contributor
    Join Date
    02-03-2007
    Location
    Burleson, TX, USA
    MS-Off Ver
    2010
    Posts
    132

    What formula to use for matches in column and return the sum of all match values in adjace

    Hello,

    I need help to find the appropriate formula to find all entries in a column (in this case C) and add all entries in an adjacent column (in this case d) each time the reference is entered. If my verbage is not clear let me know and I will try to explain better.

    Thanks,
    Mark

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: What formula to use for matches in column and return the sum of all match values in ad

    If you want to sum them up you can look at sumproduct.

    e.g.

    sumproduct((c1:c500="your text")*(D1:d500))
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    02-03-2007
    Location
    Burleson, TX, USA
    MS-Off Ver
    2010
    Posts
    132

    Re: What formula to use for matches in column and return the sum of all match values in ad

    Thanks for the help, but that formula is not working. Let me explain better:

    In column C there are multiple entries that say "purchase bedding". There are maybe 50 or 60 entries and they are scattered from C1:C2000

    In column D there are quantities for each item in column C. I would like the formula to find each instance of "purchase bedding" and sum the total quantities of all "purchase bedding" items.

    Thanks again for any help.

    Mark

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: What formula to use for matches in column and return the sum of all match values in ad

    Maybe you can post an excel file without confidentional information.

    Please also add the desired result.

  5. #5
    Forum Contributor
    Join Date
    02-03-2007
    Location
    Burleson, TX, USA
    MS-Off Ver
    2010
    Posts
    132

    Re: What formula to use for matches in column and return the sum of all match values in ad

    The file is far too large, even when zipped. See example below:

    ...............C ......................................... D
    1....Purchase Bedding..............................100
    2....Purchase Pipe....................................300
    3....Purchase Filter fabric...........................50
    4....Purchase Bedding................................60
    5....Install Pipe........................................900
    6....Purchase Bedding................................50

    What I need this to do is look for matches to "purchase bedding" and return the sum of the items in D. In this case the answer would be 210 (100+60+50). I hope that clears up my poor ability to explain.

    Mark
    Last edited by mkmed; 10-22-2012 at 05:36 PM. Reason: better clarification

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: What formula to use for matches in column and return the sum of all match values in ad

    This can be done with the sumproduct (see my earlier post).

    Change your text in purchase bedding

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: What formula to use for matches in column and return the sum of all match values in ad

    Try using a pivot table.

    Select all your information and put column D in the "row label" and "value" section.

    From there, go into the field value settings and select "count." Then simply make the formula in the column over which multiplies the two together.

  8. #8
    Forum Contributor
    Join Date
    02-03-2007
    Location
    Burleson, TX, USA
    MS-Off Ver
    2010
    Posts
    132

    Re: What formula to use for matches in column and return the sum of all match values in ad

    Thanks Nick Simo. The Pivot table works, but it takes alot of real estate. Is there a way to make it a one cell answer?

    Mark

  9. #9
    Registered User
    Join Date
    08-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: What formula to use for matches in column and return the sum of all match values in ad

    you could use a "sumif" formula. Your Range would be column C, your criteria would be "purchase bedding" and your sum range would be column D.

    You will have to use separate sumif formulas for each item on your list.

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: What formula to use for matches in column and return the sum of all match values in ad

    What about SUMIF!

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And copy down
    Something along those lines. Or am I barking up the wrong tree!!
    Last edited by Kevin UK; 10-23-2012 at 12:13 PM.

  11. #11
    Forum Contributor
    Join Date
    02-03-2007
    Location
    Burleson, TX, USA
    MS-Off Ver
    2010
    Posts
    132

    Re: What formula to use for matches in column and return the sum of all match values in ad

    Nick Simo and Kevin UK - you guys are AWESOME!! Works just like I wanted it to!

    Mark

+ 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