How to Use DCOUNT and DCOUNTA Functions in Google Sheets

Published on

DCOUNT and DCOUNTA are two database functions in Google Sheets used for conditionally counting values in structured data.

DCOUNT counts only numerical values, dates, and times, while DCOUNTA counts all types of values, regardless of their type.

You may be familiar with the COUNT and COUNTA functions, which work on ranges. COUNT counts only numerical values, dates, and times, while COUNTA counts all types of values. However, these functions do not allow for criteria-based counting.

Key Considerations When Using the DCOUNT and DCOUNTA Functions

These functions should be used on structured data, meaning there should be a header row, and no cells should be merged.

You won’t encounter any issues if you’re using the new table feature, as it adheres to these guidelines.

DCOUNT and DCOUNTA functions return the count of values from structured data, whether it’s part of a built-in table or data arranged in a structured format, similar to an SQL-like query.

Syntax and Arguments

Both functions share a similar syntax.

Syntax: DCOUNT

DCOUNT(database, field, criteria)

Syntax: DCOUNTA

DCOUNTA(database, field, criteria)

Arguments:

  • database: Structured data or a table inserted via Insert > Table.
  • field: Specifies the column in the database to operate on. It can be either the column number or the column label.
  • criteria: An array or range containing the criteria, which must also be structured.

Don’t be confused by the arguments. They are easy to understand through the following examples.

Examples of DCOUNT and DCOUNTA Functions

In the following example, we have structured data in the range A1:C as follows:

NameFeeStatus
John1500Active
Ben4500Inactive
PrashanthActive
Mike1500Active
Rose3000Inactive

Let’s find the following:

  • The number of people whose status is “Active.”
  • The number of people whose status is “Active” and whose subscription fee is paid.

Steps:

Arrange the criteria in a structured way. Since we need to apply the criteria to the last column (Status), enter the field label of the third column in any empty cell and the criteria below it. For example, enter “Status” in cell E1 and “Active” in cell E2.

Use the following DCOUNTA formula in cell G2 to count the number of active subscribers:

=DCOUNTA(A1:C, 1, E1:E2) // returns 3
DCOUNTA function example in Google Sheets, counting non-blank cells based on criteria

Where:

  • database: A1:C
  • field: 1 (the “Name” column)
  • criteria: E1:E2

Use the following DCOUNT formula to count active subscribers who have paid the subscription fee:

=DCOUNT(A1:C, 2, E1:E2) // returns 2

Here, the field is 2, which corresponds to the “Fee” column (a numeric field). The formula counts the numeric values in this column where the criteria in the third column match.

Alternatively, you can use these formulas as well:

=DCOUNTA(A1:C, "Name", E1:E2)
=DCOUNT(A1:C, "Fee", E1:E2)

Hardcoding Criteria in the DCOUNT and DCOUNTA Functions

If you prefer not to use a separate range for entering criteria (like E1:E2), you can use VSTACK, HSTACK, or a combination of both to create an array of criteria. The choice depends on your criteria and may require a detailed tutorial, which you can find in the “Resources” section below.

In the examples provided, you can replace E1:E2 in both the DCOUNT and DCOUNTA functions with the following VSTACK formula:

VSTACK("Status", "Active")

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.