Excel DCOUNT Function Examples – Excel & Google Sheets

This tutorial demonstrates how to use the DCOUNT Function in Excel.

What is the DCOUNT Function?

DCOUNT is one of Excel’s database functions. It counts the number of numerical cells within a database column, after filtering the data on a given set of criteria.

In this context, a “database” just means an organized table of data in Excel, with column headers.

How to Use the DCOUNT Function

To use the Excel DCOUNT Function, type the following:

=DCOUNT(B7:F19,"Oscars won",B3:F4)

Here we have a little database with information on a few movies. Imagine we want to know how many movies in our database were released after 2000, earned over $1bn, but didn’t win any Oscars. We can use DCOUNT for that.

DCOUNT is a little more complex than Excel’s other counting functions, so let’s walk through this step-by-step:

• First, we define the database. This is the data in range B7:F19. Note that the data has column headers, and we include these in the range.
• Next, we tell DCOUNT which column we want to count – Oscars Won. Here we’ve referred to it by the column name in quotations, but we can use its numerical position too – putting 5 here would do the same thing. If we don’t define a field name here, DCOUNT will just return the number of rows that remain after filtering.
• Finally, we tell DCOUNT what filters to apply to the data before it starts counting. We’ve set up the criteria for our filters in B3:F4.

Take a look at the criteria table in B3:F4. Notice that the column headers match the ones in our database. We just have one row here, and under each header we’ve entered the criteria we need – a “Year” greater than 2000, a “Worldwide Gross ($m)” greater than 1000, and a 0 for “Oscars Won”. DCOUNT gives us 3 results.

DCOUNT Counts Numerical Values Only

It’s important to keep in mind that DCOUNT will only count rows with numerical data in the field you define.

Our example above worked because we asked DCOUNT to count the “Oscars Won” field, which contains numbers. If we’d used “Name” as our field, DCOUNT would return 0, as there is only text string data in that column.

What You Can Use as Criteria

DCOUNT is fairly flexible, and you can define your criteria in a few different ways. Here are a few common examples:

Using Multiple Criteria Rows

You can use as many rows as you like in your criteria table.

DCOUNT uses “OR” logic for multiple rows. Effectively, this means it will get the count for each criteria row separately, add up all the counts, and return the sum.

Here’s an example:

We’re filtering for movies called “Aladdin” that were released after 2000, and movies called Titanic that were released after 1995.

DCOUNT returns 2. Why? Because if we’d used either of these criteria rows separately, DCOUNT would have returned 1 for each (Aladdin from 2019, and Titanic from 1997). So, by using both rows together, we get 2.

A couple more points to note:

• In this example we didn’t define the field. Notice that the middle section of the formula is blank:

=DCOUNT(B8:F20, ,B3:F5)

• You don’t need to include every column in your criteria table. In this example we only needed “Name” and “Year”, so that’s all I put in the table.

Counting Non-Blank Cells in a Database

As I mentioned earlier, DCOUNT only counts numerical values in the field you specify. If you need to count any cell that isn’t blank, you can use DOUNTA for that. Check out the main page on DCOUNTA <<link>> for more information.

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

DCOUNT’s ability to filter your data before counting is useful – but sometimes it’s overkill. If you don’t need that, and you just need to count cells containing numerical values in range, use COUNT instead.

See this example:

=COUNT(B3:B11)

 

Here we have a single column of data, and we used COUNT to return the number of cells with numerical values. We get 4, because Excel is counting the date in B4 as well as the three numbers (Excel stores dates as numbers).

Learn more about this function on the main page for COUNT.

DCOUNT in Google Sheets

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