< Go Back

Excel DCOUNTA Function

Want to learn more about how to count matching records in a database using Excel? This post will give you an overview of how to count matching records in a database in Excel.

Formula

The generic syntax for this formula is:

=DCOUNTA(database,[field],criteria)

  • database: Complete database range with headers
  • field [optional]: Name of field or index to count
  • criteria: Defined criteria range with headers

How the DCOUNTA function works

In Excel, the DCOUNTA function counts cells that contain non-blank values and meet a given criteria. DCOUNTA is similar to the DCOUNT function, except the DCOUNT function counts cells with a specific value while DCOUNTA counts cells without a specific value. 

Figure 1: Example of counts matching records in a database

To count matching records in a database, you can use the DCOUNTA function shown in the above Figure 1. In the example, the formula in G10 is:

=DCOUNTA(B7:E14, “Color”, B3:E4)

The Excel database functions are not case sensitive. So, for example, the field =”Color” will count cells containing the text “Color” or “color”.

Important Points

  • When Criteria match then DCOUNTA function count numbers and text in the defined field
  • In DCOUNTA function criteria argument can contain more than one row.
  • In this function, field argument can be supplied as a number which shows field index or as a name in double quotes (” “)
  • It is important to include matching headers in database and criteria ranges.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar