Excel 2007 : Table Summary

**macky1730** · 09-08-2010, 04:36 AM

Hi. need help to make a table from a list of items in a column with the same "BILL" item but with different names and values. the summary would be in the "summary" tab and the values will be entered in the grey colored cells. I hope a simple excel formula is possible

**sweep** · 09-08-2010, 05:58 AM

Hi,

In cell B2 enter this array formula

=INDEX(Table!$C$2:$C$16,MATCH(Summary!$A2&Summary!B$1,Table!$A$2:$A$16&Table!$B$2:$B$16,0))

confirm with CTRL, SHIFT and ENTER, then copy to the rest of the table.

or, use a pivot table with Name as column labels, Bill as row labels, and qty as values.

**macky1730** · 09-08-2010, 06:14 AM

Originally Posted by sweep

Hi,

In cell B2 enter this array formula

=INDEX(Table!$C$2:$C$16,MATCH(Summary!$A2&Summary!B$1,Table!$A$2:$A$16&Table!$B$2:$B$16,0))

confirm with CTRL, SHIFT and ENTER, then copy to the rest of the table.

or, use a pivot table with Name as column labels, Bill as row labels, and qty as values.

thanks

.. what does this CTRL, SHIFT & ENTER do?