# Sum the value of multiple cells off the description field

1. ## 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.

Sincerely grateful for any help.

2. ## Re: Sum the value of multiple cells off the description field

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.

3. ## 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. ## 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

5. ## 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)

6. ## 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. ## 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)
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. ## 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. ## 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. ## 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. ## Re: Sum the value of multiple cells off the description field

You are welcome

12. ## Re: Sum the value of multiple cells off the description field

Thank you!...

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

#### 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