+ Reply to Thread
Results 1 to 12 of 12

Sum the value of multiple cells off the description field

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Sum the value of multiple cells off the description field

    Hi All,

    I am new to this forum and have a question for you experts in here.

    I have two sheets in Excel that I am trying to work with. The sheet1 (FINISHED-PART) consists of part numbers of finished or packaged goods at my company. The sheet contains 4 different part numbers that each use 2 pieces of the same raw good. There are other bulk items in these finished parts that require them to have different part numbers.
    FINISHED-PARTS.jpg

    The sheet below (BULK-PART) is the one that contains the bulk part number that is consumed by all three of the finished part numbers above.
    BULK-PART.jpg

    Now trying to keep this simple and so it makes sense. What I am trying to do is SUM up the QTY AVAILABLE cell with the finished goods and have that total show in the QTY AVAILABLE cell on the bulk part sheet. The only things these parts have in common for sorting and adding is the the first 6 characters in the description in the bulk part field matches the first 6 characters in the NUM column in the finished part sheet. LOL! I hope you are following me?

    Now, this is just one raw number and the 4 finished parts that consume it. We have hundreds of parts and I will apply the fix for this to the whole sheet if anybody is able to help with this. I just wanted to scale it down and see if you can do what I am asking.

    Let me know if I need to provide more information?

    Sincerely grateful for any help.

  2. #2
    Registered User
    Join Date
    03-02-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Sum the value of multiple cells off the description field

    1) Please submit the sample worksheets instead of images.
    2) Provide clearly which column you want to fill with a formula and the value you are expecting to be summed up from the other sheet.

    This will help you get a quick reply from members.
    If I have helped, click on the * below the post

  3. #3
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    Thanks for the help and advice. I have attached the file.

    TEST-FORECASTER-2.xlsx

    I need the formula to be in the cells in COLUMN I on sheet (BULK-ITEMS). It needs to return the summed values of the cells in COLUMN G on sheet (FINISHED-GOOD).

    This needs to index of off 13001C in the description column in sheet (BULK-ITEMS) and look for all the finished good part numbers in sheet (FINISHED-GOOD)that have the first 6 characters of 13001C.

    Hope this gets the ball rolling.....

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Sum the value of multiple cells off the description field

    Try this:
    In 'BULK-ITEMS' sheet I2:
    =SUMPRODUCT((LEFT(B2,6)=LEFT('FINISHED-GOOD'!$A$2:$A$5,6))*'FINISHED-GOOD'!$G$2:$G$5)

    not sure if that is what you are looking for, but as far as I can tell from your post(#3) that is what you are asking for...

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sum the value of multiple cells off the description field

    You could use SUMIF with a "wildcard" like this:

    =SUMIF('FINISHED-GOOD'!A:A,LEFT(B2,6)&"*",'FINISHED-GOOD'!G:G)
    Audere est facere

  6. #6
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    This does add up the cells but can you create a formula that matches the 13003C in the finished good to the 13003C in the bulk item?

    If I drag this formula down on my master sheet it just adds up random quantities and does not pay attention to matching the the first six characters in the finished part number to the first six characters in the description of the bulk part.

    I attached another file to try. The goal here is to try and get all of the finished good part numbers that start with 13003S and 13003c to sum their values from column G on the FINISHED-GOOD sheet in their respective cell in the BULK-ITEM sheet which would be column I. They need to index off of 13003S and 13003C in the description because that is the only link between the bulk item and the finished item.

    The problem on my master sheet is that the part numbers are scattered through the sheet and not in numerical cell order within the sheet.

    TEST-FORECASTER-3.xlsx

    Much thanks for all the input and help so far. You guys are awesome! As you can tell I am very much a rookie at this but I love it.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sum the value of multiple cells off the description field

    Quote Originally Posted by daddylonglegs View Post
    You could use SUMIF with a "wildcard" like this:

    =SUMIF('FINISHED-GOOD'!A:A,LEFT(B2,6)&"*",'FINISHED-GOOD'!G:G)
    I think my formula works for that (and so will dredwolf's if you extend the range to cover more rows) - if I put my formula in I2 I get -196. If I copy it to I3 I get -400 - are those the values you expect?

  8. #8
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    Yes, these are definitely working in the sample worksheets that I provided. Very cool! But for some reason they are not working in the master file that I am trying to apply them to. I am much closer than I have ever been but I must not be recreating the scenario exactly how my master file is laid out.

    I will research my master file more to try and see what information I am not providing.

    Once again thanks for the help. You guys rock!

  9. #9
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    I just wanted to say that after making some adjustments to the query that my spreadsheet was doing, the formulas you guys provided are working like a charm!!!

    I do have one last request on the formulas that you produced? Can you make it so it only sums up negative numbers?

  10. #10
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    =SUMIF(I21:K21,"<0")

    I got it.....thanks all!!! you are awesome! Glad to be part of the family. I know my skills will improve with a little help from yall!!

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Sum the value of multiple cells off the description field

    You are welcome

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

  12. #12
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Sum the value of multiple cells off the description field

    Thank 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