This Tutorial demonstrates how to use the Excel DCOUNTA Function in Excel.

DCOUNTA Function Overview

The Excel DCOUNTA function counts matching records in a database using criteria and an optional field.

To use the DCOUNTA Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

DCOUNTA function Syntax and inputs:

=DCOUNTA (database, [field], criteria)

Database – Database range including headers.

Field – [optional] Field name or index to count.

Criteria – Criteria range including headers.

What is the DCOUNTA Function?

DCOUNTA is one of Excel’s database functions. It returns the number of non-blank cells in a database column, after first filtering the data based on criteria that you specify.

Note that as far as DCOUNTA is concerned, a “database” is simply a table of data within your spreadsheet, set up with column headers.

How to Use the DCOUNTA Function

You use the Excel DCOUNTA Function as follows:

=DCOUNTA(B7:F19,5,B3:F4)

So, here we have a little database with some movie data. We’d like to know how many movies in the database were released before 2010, grossed less than $1bn worldwide, and won at least one Oscar.

We’re using the DCOUNTA Function to get that information, so let’s break it down step-by-step:

1. The first range defines our database: B7:F19. Note that we’ve included the column headers in this range.
2. The next part of the function tells DCOUNTA which field of the database to count. We’ve asked for field 5: Oscars Won. You can also refer to the field by putting its name in quotation marks like this: “Oscars Won”. This step is optional – if you don’t define a field here, DCOUNTA will simply returns the number of rows that remain after filtering.
3. The last part for the function is the range containing the criteria we want to use to filter the data. We’ve put these in B3:F4.

As you can see in the criteria range B3:F4, the column headers are identical to the ones in the database. If the headers don’t match, the filter won’t have any effect. Note that you don’t have to include every column header – you can just include the ones you need.

Our criteria are a “Year” less than 2000, and a worldwide gross less than 1000. Because the movies that didn’t win Oscars have blank cells in column 5, DCOUNTA counts only those who did win Oscars, and returns 4.

What You Can Use as Criteria

With DCOUNTA, you have a lot of scope as to what you can use in your criteria. Here are a few of the more common examples:

Using Multiple Criteria Rows

Your criteria table can have more than one row. If you do use more than one, DCOUNTA will use “OR” logic when filtering.

In effect, this means DCOUNTA will filter each row separately, and sum the results.

See the example below:

We’re filtering for:

• Movies that are called “Aladdin” and were released after 2000
• Movies that are called “Titanic” and were released after 1995

As with the previous example, we’re counting any rows in the database that meet these criteria, and for which column 5, the “Oscars Won” column, is not blank.

This time DCOUNTA returns 1, because only Titanic (1997) meets all of our criteria.

Notice here how the criteria table only included the two fields we needed – this is perfectly valid – you don’t need to include all fields in your criteria table.

Counting Numerical Cells in a Database

As noted earlier, DCOUNTA only counts non-blank values in the field you specify. If you need to count only the cells that contain numerical data, Excel has another database function for that – DCOUNT.

You can learn more about this function on the main page for DCOUNT. <<link>>

Counting Non-Blank Cells that Aren’t in a Database

If, instead of filtering an entire database, you just want to count the number of non-blank cells in a range as it appears on your spreadsheet, you can use another function for that: COUNTA.

Here’s an example:

=COUNTA(B3:B11)

So here we just have one column of data, B3:B11, and we want to know how many non-blank cells there are in it. There are nine cells in total, and we only have one blank cell, so COUNTA returns 8.

You can learn more about this function on the main page for COUNT. <<link>>

DCOUNTA in Google Sheets

The DCOUNTA Function works exactly the same in Google Sheets as in Excel: