For those who are familiar with DSUM, no need to spend extra time to learn the use of DCOUNT and DCOUNTA functions in Google Sheets. All these are Database Functions and follows the same pattern in arguments.
Earlier I’ve published a complete tutorial featuring All count functions in Google Sheets. In that list also you can see some advanced tips related to DCOUNT and DCOUNTA.
The purpose of DCOUNT and DCOUNTA functions in Google Sheets is to conditionally count column ranges. Then how they differ?
The Purpose of the DCOUNT Function in Google Sheets
Use Google Sheets DCOUNT function to count numeric values from a database or range using an SQL-like query.
The Purpose of DCOUNTA Function in Google Sheets
The purpose of DCOUNTA function is almost the same as the DCOUNT. The difference here is the former counts numeric as well as text values but the latter counts only numeric values.
You can consider this post as an answer to your following queries.
1. Google Sheets DCOUNT Function and Formula usage – How to?
2. Google Sheets DCOUNTA Function and Formula usage – How to?
So here we go.
How to Use DCOUNT and DCOUNTA Functions in Google Sheets
Syntax: DCOUNT
DCOUNT(database, field, criteria)
Syntax: DCOUNTA
DCOUNTA(database, field, criteria)
Database
The data range to consider. It must be structured that means the first row should contain the labels for each column’s values. No merged cells are entertained.
Field
Indicates which column, put either column number or column label that within the double quotation marks, in the database contains the values to be extracted and operated on.
Criteria
An array or range containing criterion/criteria by which to filter the database values before operating.
Sample Data and Formula to Explain the Dcount and Dcounta Syntaxes
In the below screenshot the values inside the cyan color box are the criteria and the values inside the dark red berry box are the database aka range. The number 4 in the formula represents the field.
Formula:
=DCOUNTA(A6:D16,4,A2:D3)
Formula Explanation:
The above DCOUNTA formula counts column D if Column D value is “Absent” and Column C value is “Planning”.
Here I’ve used DCOUNTA function since the column D values are in Text format. If it’s numeric, you should use DCOUNT.
With the above formula, I could easily find the number of persons absent from the Planning Division.
This’s just a basic example of the use of DCOUNTA in Google Sheets. You can use comparison operators in the criteria field. Also, you should know how to use the date as criteria in DCOUNT as well as in DCOUNTA.
I’ve already posted the same for DSUM. I am not going to repeat the same here as all the Database functions including DCOUNT and DCOUNTA follow that. So I’m just pointing you to the relevant tutorial below.
Reference: How to Properly Use Criteria in DSUM in Google Sheets