DCOUNTA function, how to use the function to count non-empty cells in Excel
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.
- Excel formulas and functions
- How to use the COUNTIF function to count the number of cells that satisfy the condition on Excel
- How to use the COUNT function in Excel
- VLOOKUP function to use and specific examples
Instructions for using DCOUNTA function on Excel
Syntax of DCOUNTA = Dcounta function (database, field, criteria) .
Inside:
- Database: cell range or list or database, the first row of the list must contain a title for each column.
- 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.
- 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:
- 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.
- Do not set the condition range below the list because new information will be added at the end of the list.
- The condition range does not override the list.
- 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.
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:
- A6: D12: is the data area containing the data to count.
- D6: data column to check conditions.
- C2: C3: data area contains conditions.
The result for that request is 4 discounted items.
Example 2: Calculate the total product price increase.
Then the condition for our spreadsheet must also change to Increase * as shown in the picture.
In the input box, enter the formula function = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.
Results showing 2 products have increased selling prices.
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.
At the input box, enter the formula = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.
The results show that the total volume of products increased by 4. The cells that have no value are removed without counting.
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.
Download the input box and enter the formula = DCOUNTA (A6: D12, D6, C2: C3) and press Enter.
As a result, there are 3 items whose prices change more than 10%.
In this case we can also change the filed field to be the number of the column in the data table, here is 4.
Or replace it with the column name, Note, it also shows the result of 3.
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!
You should read it
- Basic Excel functions that anyone must know
- How to use the SUM function to calculate totals in Excel
- How to use the MOD function and QUOTIENT function in Excel
- How to use COUNTIF function on Excel
- How to use the SUBTOTAL function in Excel
- How to use the LEN function in Excel
- How to use Hlookup function on Excel
- How to use SUMPRODUCT function in Excel
- The COUNTIFS function, how to use the cell count function according to multiple events in Excel
- MS Excel 2007 - Lesson 6: Calculation in Excel
- Round function, how to use rounded functions in Excel
- How to use the Match function in Excel
Maybe you are interested
Instructions for quickly aligning Excel printed pages, printing to fit the paper, without losing columns
Excel - Convert columns to rows in Excel
How to delete rows and columns in Excel with mouse or key
Instructions on how to break columns in Word simply and quickly
Column order in Excel is reversed, why and how to handle it?
Excel does not allow inserting additional columns and rows [FIXED]