How to Use Excel's DCOUNTA Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's DCOUNTA function is short for "database count all." It considers all of the cells in a column of a database that match the specified criteria. DCOUNTA then returns a count of all nonblank cells in the same manner as COUNTA, the equivalent non-database function. The following steps demonstrate how to use Excel's DCOUNTA function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DCOUNTA. It is DCOUNTA(database_array,field,criteria_array). Database is an array that defines the database, field is a column name or number within that database that DCOUNTA will count if it is nonblank and criteria is an array containing the criteria to be used in selecting rows.

Step2

Study the cell values that will be counted by DCOUNTA. Cells will be counted as long as they contain any information at all, even empty text or error values.

Step3

Use the DCOUNT function if you do not wish to count error values, logical values or text.

Step4

Enter the following in the first four columns of an Excel spreadsheet:

Row 1: Tree, Height, Age and Yield;
Row 2: ="=Apple" and =">10" (nothing in columns three and four);
Row 3: ="=Cherry" and ="<18" (nothing in columns three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18 and 20 (nothing in column four);
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13 and 14 (nothing in column four);
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8;
Row 10: Apple, 8, 9 and 6.

Step5

Interpret the formula =DCOUNTA(A4:D10,"Yield",A1:B2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. "Yield" is the column name from which values will be counted. The criteria are given by A1:B2 which indicates that the column named Tree must contain "Apple" or "Cherry" and the column named Height must contain a value greater than 10 and less than 18. DCOUNTA(A4:D10,"Yield",A1:B2) will therefore consider the following two records and return 1: Cherry, 13, 14; Apple, 14, 15, 10.