SUM Totals

[swiftworks]

I would like to have a part number(column A) order qty combined by by the total of a certain qty. required (column d) combined throughout the entire worksheet. Maybe an extra sheet at the end stating all the part numbers (duplicates removed with the totals of the part number quantity total retained from the entire worksheet. I tried Find, sum IF, match. I believe I'm going about this incorrect, any help would be appreciated! thanks!Purchase.xlsx

[BB1972]

swiftworks, can you upload a workbook which shows your expected results (manually mocked-up is fine), as I'm not at all clear on what it is you're asking for.

[swiftworks]

The workbook represents a part number and the quantity that is required to build one unit(Column C). Column D represents the amount required to build for the month of March. Column A represents the part number that is required to order. The sheets at the bottom represent each unit. I would like to combine the total amount of the quantities needed for all units for the month of March based upon each part number individually. I could combine each sheet into one sheet and sort by column A numerically and then add up the amounts, but that seems like it could be simplified. The end result is I would like to see a part number and the qty. required (all sheets containing that part number) in one field, for purchasing reasons. Does this make sense?

[popipipo]

http://www.myonlinetraininghub.com/e...ple-worksheets

Take here a look

[BB1972]

One way to do this would be to copy Column A from eg your first sheet into Column A on a totals sheet. Column B could be your # required for March - pasting

Please Login or Register  to view this content.

down the same length as Column A data will return the number of items needed for each part for March. $D$2:$D$2000 & $A$2:$A$2000 references are in as you have merged cells at the top of your sheets, which don't play nice with formulas - adjust these ranges as necessary. Repeat (on the same totals tab) for all "units" / worksheets. Assuming there are parts which are used for more than one type of "unit", make a master list of unique part #s, and use a SUMIF formula to total the number of parts required, based on the part #.

Does this make sense?

[swiftworks]

Purchase.xlsx

Here is the file

[BB1972]

Attachment 217488

Here is the file

? I'm not sure if that's addressed at me, or poipipo, but it appears to be the same file as you uploaded in post # 1. Can you let me know if the advice I gave you in post # 5 made sense to you? Is this something that would work for you?

[swiftworks]

I am somewhat confused on your response Brendan.

[swiftworks]

Book1.xlsx

This is a master file of everything required I would just like to add the totals p/ part number without manually having to go through it

[BB1972]

I am somewhat confused on your response Brendan.

Indeed. Have a look at the attached, which will hopefully (among other things) demonstrate how a file showing expected results can often be more helpful than a description of what is required - precisely the reason I asked for one.

On the "TOTALS" tab that I have added, to keep things as straightforward as possible, I have copied the part numbers and the quantity required from L10-0011-000 & L10-0003-000, and pasted to Columns A & B. The results, as I understand your requirement, are in Columns E & F. I just copied Column A to Column E, then used the Remove Duplicate function on the Data tab to get a list of unique part numbers - each part number only being listed once.

From there, using SUMIF can return the number of parts required for each part number, using:

Please Login or Register  to view this content.

in F3, copied down as far as necessary. The result in Columns E & F is a list of the part numbers required, and the number/quantity of each part required.

Hope this helps.