Excel 2007 : Sum numbers from a table

[chinox]

Hi,
I am struggling here, and have spend hours to lookup formula to do the calculation with no luck. I have a table looks like following:


COLOR S M L XL
NAVY 297 297 198 0
PURPLE 297 297 198
PINK 297 297 198
BLACK 297 297 198
NAVY 96 96 64
PINK 81 81 54
PURPLE 57 57 38
NAVY 100 102 25
PURPLE 75 26
PINK 106 10
BLACK 68 27

-----------------------------------------------
And I need to make a summary table looks like
Size Color Total Qty
S Navy 493
S Black
S Pink
S Purple


Can some one tell me whats the formula to put in Total Qty that will automatically lookup and sum the number in the table. Right now I have to calculate total by hand. Any help is grateful.

[Ron Coderre]

Try using a Pivot Table

• Select the range of data
• Insert.Pivot Table...select where you want the pivot table...Click: OK
• Drag the COLOR field to the Row Labels section
• Drag each SIZE field to the Values section
• Done

Is that something you can work with?

[chinox]

Thank you for your suggestion.
Pivote Table is probably not gonna work because the table is only a part of invoice. The original data is a part of packing listI was hoping to have a formula with sumif or sumproduct that can lookup the table.

[darkyam]

Say your data is A1:E12 and your new table headers start on row 14 (so first row of data is in 15). You can use =SUMPRODUCT(($B$1:$E$1=A15)*($A$2:$A$12=B15)*($B$2:$E$12)) in C15 and copy down as needed.

[chinox]

IT WORKS!!! Thank you very much! This will save me a lot trouble to calculate the total qty. The raw data is from packing list, and summary is for invoice. It takes allot time to calculate ttl qty when there is over 100 boxes. Once again, thank you so much. I will apply this to my workbook now, and hopefully it will generate PL and Invoice automatically.

[darkyam]

You're welcome. Glad it worked for you.