Microsoft KB Archive/78197

{|
 * width="100%"|

Excel: Using a Data Table to Summarize Database Information

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2 version 2.2, 3.0
 * Microsoft Excel for the Macintosh, version 2.x, 3.0, 4.0

-

SUMMARY
You can use the Microsoft Excel Data Table command to summarize information for several field names in a Database more efficiently than setting several independent criteria and several functions. The example below illustrates how a data table can perform this task with the DSUM function.

MORE INFORMATION
This example summarizes information for two different products listed in a Microsoft Excel database.

Microsoft Excel version 5.0
  Enter the following database, criteria, and data table information into a spreadsheet:

A1: Product                      B1: Quantity    C1: Sales A2: bolts                        B2:    50       C2: $3.00 A3: bolts                        B3:    70       C3: $2.00 A4: nuts                         B4:    60       C4: $1.00 A5: nuts                         B5:    80       C5: $1.00 A6:                              B6:             C6: A7: Product                      B7: Quantity    C7: Sales A8: nuts                         B8:             C8: A9:                              B9:             C9: A10: =DSUM(Database,B7,Criteria) B10: Quantity   C10: Sales A11: nuts                        B11:            C11: A12: bolts                       B12:            C12:  Select the range A1:C5. From the Insert menu, choose Name, and then choose Define. In the Names In Workbook box, type Database and choose Add. Clear the contents of the Refers To box by selecting it and pressing the DELETE key. With the insertion point in the Refers To box, select the range A7:C8 on your worksheet. In the Names In Workbook box, type Criteria and choose Add. Choose Close. Select the range A10:C12. From the Data menu, choose Table. In the Row Imput Cell box, type B7. In the Column Imput Cell box, type A8. Choose OK.

Microsoft Excel versions 2.x, 3.0 and 4.0
  Enter the following database, criteria, and data table information into a spreadsheet:

A1: Product                      B1: Quantity    C1: Sales A2: bolts                        B2:    50       C2: $3.00 A3: bolts                        B3:    70       C3: $2.00 A4: nuts                         B4:    60       C4: $1.00 A5: nuts                         B5:    80       C5: $1.00 A6:                              B6:             C6: A7: Product                      B7: Quantity    C7: Sales A8: nuts                         B8:             C8: A9:                              B9:             C9: A10: =DSUM(Database,B7,Criteria) B10: Quantity   C10: Sales A11: nuts                        B11:            C11: A12: bolts                       B12:            C12:  Select the range A1:C5. From the Data menu, choose Set Database.</li> Select the range A7:C8. From the Data menu, choose Set Criteria.</li> Select the range A10:C12. Choose Table from the Data menu. In the dialog box, enter B7 as row input cell and A8 as column input cell.</li></ol>

Results
The total quantities and sales for nuts and bolts are returned in cells B11:C12. The results are as follows:

<pre class="FIXEDTEXT">B11: 140  C11: 2 B12: 120  C12: 5