Excel: Summing a Field in a Database Based on Criteria

ID: Q24766

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0, 5.0, 5.0a

SUMMARY

To sum all the entries in a database field based on whether the record containing the field matches certain criteria, use the DSUM() function as follows


   =DSUM(Database,"fieldname",Criteria)

where "fieldname" is the name of the field containing entries to be summed (this name must be enclosed in quotation marks).

As an alternative to indicating a field name in the second argument, you can type the index number of the field to be summed that corresponds to the order of the field names in the database. For example, if cell A1 contains the field name "Name," and cell B1 contains the field name "Number," the index of "Name" would be 1 and the index of "Number" would be 2.

MORE INFORMATION

If the field "Cost" is the second field in the database, the DSUM() function can be entered as follows:


   =DSUM(Database,"Cost",Criteria)

   -or-

   =DSUM(Database,2,Criteria)

Database is the range of cells that make up the database (note that this range can be referred to with a defined name).

Criteria is the range of cells that contain the database search criteria (note that this range can be referred to with a defined name).

For additional information about using a database in Microsoft Excel, please see the following article(s) in the Microsoft Knowledge Base:

Q98910 : AppNote XE0186: Database Tips

Additional query words: howto 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.20 3.0 5.00a

Keywords :
Version :
Platform :
Issue type :