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.
The generic syntax for this formula is:
- 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”.
- 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.