Style number / part number auto-populate in 2nd table

[eareeyesee]

I am a novice in excel (2010). I own a t-shirt brand, when I put together a print run with multiple designs, that have multiple colored shirts per design (as shown in my screenshot). I need to then total up the number of: SMALL BLACK-T, MEDIUM BLACK-T, SMALL BLUE-T, ETC from all designs combined, so I know how many total of each size/color to order from the supplier.

Normally I manually transpose each BLACK-T row from each individual design, into a BLACK-T column (BLUE-T column, etc) & then sum those totals.

I'm curious if there is a way to some how tag garment names (part number, item code, serial number?), so that every row that uses BLACK-T in the design column, will automatically populate in the individual BLACK-T column, so that it will automatically total all black t's from a PO of 10 assorted designs. And the same for the other color garments?

Sorry I do not know a more concise way to ask this question. Refer to my attached image for visual representation.



print screen windows xp

 

[Michael M]

I can't see the image....but from the description you have provided a PivotTable might do the task for you.
If that's not the case, try uploading the wrksheet to a hosting site like DropBox or Mediafire and then post the link back here

 

[eareeyesee]

Thank you Michael. Does this direct link to the screenshot work for you: http://s23.postimg.org/q0wu706yz/EXCEL.jpg

 

[eareeyesee]

Attached is the excel file: Free file upload and exchange service without registration and size limits Above the red line is the section where I'd list all designs. Below the red line is where i'd like all the corresponding rows of each shirt color to populate. Doesn't have to be in this same exact format. Thanks.

 

[L. Howard]

Try this.


In a cell =SUMPRODUCT(($A$1:$A$21=I1)*($B$1:$B$21=I2))

Where cell I1 is a drop down of the Designs, I Heart NY, Skull etc.
Where cell I2 is a drop down with the Color-Garment, Black T, Blue T etc.

Howard