What Is DCOUNTA In Google Sheets?
The Database COUNT All, or the DCOUNTA in Google Sheets, helps users count the number of cell values in the selected dataset, whether textual or numeric, that satisfies the given criteria.
The DCOUNTA In Google Sheets is an alternative to the COUNTA, COUNTIF and the COUNTIFS, meaning, it performs all calculations that these function individually perform. For example, we have the products data and their units sold on different dates. We will count how many times the smartphones were sold in the three dates, using the DCOUNTA in Google Sheets.
Select cell F5, enter the formula =DCOUNTA(A1:C10,”Units”,E4:E5) and press “Enter”, as shown.
The output is “3”, i.e. the smartphones were sold on 3 dates. The highlighted values are the cells that satisfy the criteria, for our reference.
Key Takeaways
- The DCOUNTA in Google Sheets is a database function which returns the count of all the selected values, if the values fulfills the given criteria.
- We have three arguments for the function where all three are mandatory arguments. We will get an error or the formula will not execute if even one of the arguments is not provided.
- Itis an alternative formula for the COUNTA, COUNTIF and COUNTIFS functions except that DCOUNTA is database and supports queries too.
- A separate table, the criteria table or helper table, is created apart from the dataset to help us select the cell range to get accurate results.
Syntax
The syntax of the DCOUNTA Formula in Google Sheets is,
Themandatoryarguments of the DCOUNTA Formula in Google Sheets are,
- database: It is the cell range or the dataset selected in a way that the first row consists the field names.
- field: It is the column name we want to extract the data from and operate on that can be given as a cell value or a cell reference.
- criteria: It is the range containing single or multiple conditions to check and filter the data.
How To Use DCOUNTA Function In Google Sheets?
We can use the DCOUNTA Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon –
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Database” option right arrow – select the “DCOUNTA” function, as shown below.
Step 2: The “DCOUNTA” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the formula in the worksheet maually –
Step 1: Select an empty cell for the output.
Step 2: Type =DCOUNTA( in the cell, as shown below. [Alternatively, type =D or =DC and double-click the DCOUNTA from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some of the DCOUNTA in Google Sheets examples and count the values that fulfils the set criteria.
Example #1 – Count the number of employees with a specific job title
The dataset given below consists of employee details, such as their names, department, ID’s job role, etc, and a helper dataset in cells G1:G2. We will count the number of employees with a specific job title using the DCOUNTA in Google Sheets.
The procedure to count the number of employees with a specific job title using the DCOUNTA is,
Select cell H2, enter the formula =DCOUNTA(A1:E11,”Job Title”,G1:G2) and press “Enter”, as shown below.
The output is shown above, as 4, because out of 10 employees 4 of them are “Analyst”, i.e., these 4 options satisfy the given criteria. We have highlighted the criteria cells for reference.
Example #2 – Count the number of products above a certain quantity
The dataset given below consists of three smartphone models, the units ordered on three particular dates, and a helper dataset in cells A11:B12. We will count the number of products above a certain quantity using the DCOUNTA in Google Sheets.
The procedure to count the number of products above a certain quantity using the DCOUNTA is,
Select cell B12, enter the formula =DCOUNTA(A1:C9,C1,A11:A12) and press “Enter”, as shown below.
The output is shown above, as 4, because out of 9 values 4 of them are “>250”, i.e., these 4 options satisfy the given criteria. We have highlighted the criteria cells for reference.
Example #3 – Count the number of sales transactions for a particular product
Consider the dataset with the 2023 sales of 3 products, and a helper dataset in cells D3:E4. Let us count the number of sales transactions for a particular product using the Google Sheets DCOUNTA.
The procedure to count the number of sales transactions for a particular product using the DCOUNTA is,
Select cell E4, enter the formula =DCOUNTA(A1:B11,”2023 Sales”,D3:D4) and press “Enter”, as shown below.
The output is shown above, as 3, because out of 10 values 3 of them are P2, i.e., these 3 options satisfy the given criteria. We have highlighted the criteria cells for reference.
Example #4 – Count students who submitted their assigments on time
Consider the dataset student details, such as their names, assignment submission date and the date the students submitted and a helper dataset in cells G1:H2. Let us Count students who submitted their assignments on time using the Google Sheets DCOUNTA.
The steps to Count students who submitted their assignments on time using the DCOUNTA are,
Step 1: First, let us check if the submissions are on time.
Therefore, select cell E2, enter the formula =IF(C2=D2,”Submitted On Time”,”Late Submission”), press “Enter” and drag the formula from cells E2 to E11 using the fill handle, to get the following results.
Step 2: Now, let us Count students who submitted their assignments on time.
Therefore, select cell H2, enter the formula =DCOUNTA(A1:E11,E1,G1:G2) and press “Enter”, as shown below.
The output is shown above, as 8, because out of 10 students 8 of them have “Submitted on time”, i.e., these 8 students satisfy the given criteria. We have highlighted the criteria cells for reference.
Important Things To Note
- If you place the criteria range below the list, ensure not to add information to the list, as it will push the cells down and return error as the cell references change. To avoid such situations, it’s better to add it at the right of the dataset, unless we do not add extra columns.
- The criteria range should not overlap the list. Ensure to leave a row or a column in-between the dataset and the criteria table, if we are creating the table below or beside the dataset, respectively.
- If we do not set the specified criteria, the function generates the count of the overall column.
- We get the “#Value” error, if one of the argument values is missing.
- We get the “#N/A” error, if there is a mistake in the formula, which indicates that the it cannot perform the calculation.
- Ensure to add the direct cell values within double-quotes, to add as text.
Frequently Asked Questions (FAQs)
The Google Sheets DCOUNTA isn’t working because of the following reasons:
a. The argument values or cell reference are not organized properly.
b. The cell range we select is modified or deleted.
c. The cell value for the arguments given directly are not inserted within double-quotes.
d. Criteria range is created below the dataset and new data is added in the dataset.
We often forget in which category a function falls, here, the “DCOUNTA” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “DCOUNTA” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the DCOUNTA in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “DCOUNTA” function, as shown below.
• The COUNTA function counts the number of non-empty cells within a given range, including both text and numbers, whereas the DCOUNTA function is a “Database Count All” used for counting records in a database-like format based on certain criteria or conditions specified by the user.
• The COUNTA function has two parameters: Value1 and Value 2. The DCOUNTA function has three parameters: the database range, the field to count, and the criteria range.
Download Template
This article must help understand DCOUNTA in Google Sheets with itsformula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is DCOUNTA In Google Sheets. We learn how to use DCOUNTA along with its syntax to count cell values with detailed examples. You can learn more from the following articles. –
WEEKDAY Function In Google Sheets
Leave a Reply