Excel: Using a Data Table to Summarize Database Information

ID: Q78197

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.

Select the range A7:C8. From the Data menu, choose Set Criteria.

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.

Results

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


B11: 140   C11: 2
B12: 120   C12: 5

REFERENCES

"Function Reference," version 4.0, pages 91-93
"User's Guide 2," version 4.0, pages 6-10
"Function Reference," version 3.0, pages 54-56
"User's Guide," version 3.0 for Windows, pages 269-272
"User's Guide," version 3.0 for the Macintosh, pages 261-264

Additional query words: daverage dcount dcounta dmax dmin dproduct dstdev dstdevp dvar dvarp

Keywords :
Version :
Platform :
Issue type :