Excel: Summing a Field in a Database Based on Criteria

 Last reviewed: October 13, 1995
Article 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: 

   ARTICLE-ID: Q98910
   TITLE     : AppNote XE0186: Database Tips

KBCategory: kbother
KBSubcategory:

Additional reference words: howto 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2
2.20 3.0 3.00 4.00 5.00 5.00a

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 13, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.