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.
DCOUNTA 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:
- A6: D12: is the data area containing the data to count.
- D6: data column to check conditions.
- C2: C3: data area contains conditions.
DCOUNTA 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 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 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 5
Results showing 2 products have increased selling prices.
DCOUNTA 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 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 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 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 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 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 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 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 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!
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
May be interested
The SUMIFS function, how to use multiple conditional calculation functions in Excel
Differentiate between SUM, SUMIF, SUMIFS and DSUM functions
Instructions for writing vertical letters in Word
How to compare two documents in Google Docs
How to capitalize the first letter in Excel
How to use themes in Excel