DCOUNTA function, how to use the function to count non-empty cells in Excel

DCOUNTA functions in Excel to count non-empty cells in database fields or lists according to specific conditions.

Excel functions are divided into different groups such as the statistical function group, the text function, the database function, . The DCOUNTA function belongs to the database function, used to count non-empty cells in the field (column) of the list or database with conditions specified before. Previously, Network Administrator once instructed you to read how to use DCOUNT function in Excel, but used to calculate the number of numeric elements on the column of the data area. The following article will guide you how to use the DCOUNTA function in Excel.

  1. Excel formulas and functions
  2. How to use the COUNTIF function to count the number of cells that satisfy the condition on Excel
  3. How to use the COUNT function in Excel
  4. VLOOKUP function to use and specific examples

Instructions for using DCOUNTA function on Excel

Syntax of DCOUNTA = Dcounta function (database, field, criteria) .

Inside:

  1. Database: cell range or list or database, the first row of the list must contain a title for each column.
  2. Field: column used in the function, or column header if you enter the column heading directly in quotation marks, or you can enter the sequence number of the column with the first column is column 1.
  3. Criteria: range of conditional cells. You can use any range but contain at least 1 column header and 1 cell containing conditions under the column header box.

Note when using DCOUNTA in Excel:

  1. You can use any range for the criteria argument but must contain at least 1 column header and 1 cell containing the condition under the column header box.
  2. Do not set the condition range below the list because new information will be added at the end of the list.
  3. The condition range does not override the list.
  4. To manipulate an entire column in the list, enter a blank line below / above the column header in the range of conditions.

We will apply to the data statistics table as below.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 1DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 1

Example 1: Calculate the total number of discounted items.

In the input box, enter the formula = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.

Inside:

  1. A6: D12: is the data area containing the data to count.
  2. D6: data column to check conditions.
  3. C2: C3: data area contains conditions.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 2DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 2

The result for that request is 4 discounted items.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 3DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 3

Example 2: Calculate the total product price increase.

Then the condition for our spreadsheet must also change to Increase * as shown in the picture.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 4DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 4

In the input box, enter the formula function = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 5DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 5

Results showing 2 products have increased selling prices.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 6DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 6

Example 3: Calculate the number of products with variable prices

When you sum up all products, you will count the valid cells. Change the condition to a blank as shown below.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 7DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 7

At the input box, enter the formula = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 8DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 8

The results show that the total volume of products increased by 4. The cells that have no value are removed without counting.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 9DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 9

Example 4: Calculating the number of products changing prices by more than 10%

The current condition will change to * 1?%, Characters? to replace 1 numeric character, * replace any string.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 10DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 10

Download the input box and enter the formula = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 11DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 11

As a result, there are 3 items whose prices change more than 10%.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 12DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 12

In this case we can also change the filed field to be the number of the column in the data table, here is 4.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 13DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 13

Or replace it with the column name, Note, it also shows the result of 3.

DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 14DCOUNTA function, how to use the function to count non-empty cells in Excel Picture 14

So you know how to use the DCOUNTA function on Excel to calculate non-empty values. The field entry may change to many different types, but the condition field must be entered correctly.

I wish you all success!

4.6 ★ | 5 Vote