DCOUNTA

DCOUNTA(database, field, criteria)

Returns the total number of non blank cells in a database column satisfying certain conditions (including logical values and text).

database	The range of cells that make up the database or list.
field	The column name or number indicating which column to use.
criteria	The range of cells that contain the conditions.

REMARKS

* This is identical to the DCOUNT function except that text, logical values and errors ARE INCLUDED.
* This function will only include text, logical values and errors when the cells are counted once the criteria has been matched.
* The "database" can be a cell reference or a named range.
* The "database" range of cells must contain column headings in the first row. See Example 19.
* The "field" can be a column label, a column number (left to right) or a cell reference.
* The "field" label is not case sensitive. See Example 12.
* If "field" is left blank, then the total number of rows matching the criteria is returned. See Example 20.
* If the "field" column contains some text entries, then these are ignored. See Example 7.
* The "criteria" must include at least one column heading and one cell below the column heading. See Example 16.
* If "criteria" is left blank, then #VALUE! is returned. See Example 21.
* The "criteria" must be in a contiguous block of adjacent cells.
* The "criteria" can be anywhere on the active sheet, although it is recommended not to put it below the data, in case more data is added later on.
* Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column.
* To perform an operation on an entire column in a database, enter a blank cell below the column heading in the "criteria" range. See Example 11.
* If no rows match the criteria, then 0 is returned. See Example 14.
* For an illustrated example refer to the Specifying Database Criteria page.
* It is possible to use the data from a pivot table. When the "database" range refers to a cell inside a pivot table, the calculation is only performed on the data currently displayed in the pivot table.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=DCOUNTA(B1:D10,"Age",B12:B13) = 1	Name	Age	Salary
2	=DCOUNTA(B1:D10,2,B12:B13) = 1	John	19	18,000
3	=DCOUNTA(B1:D10,"Salary",B12:B14) = 2	Mark	20	24,000
4	=DCOUNTA(B1:D10,"Age",C12:C13) = 6	David	17	16,500
5	=DCOUNTA(B1:D10,"Age",C12:C14) = 8	James	22	20,000
6	=DCOUNTA(B1:D10,"Salary",C12:D13) = 2		18	19,500
7	=DCOUNTA(B1:D10,"Age",D12:D13) = 4	Nick	21	24,000
8	=DCOUNTA(B1:D10,"Salary",D12:D13) = 4	Matthew	text	26,500
9	=DCOUNTA(B1:D10,"Salary",B16:D17) = 1	Jamie	17	18,500
10	=DCOUNTA(B1:D10,"Salary",B12:D14) = 1	Mark	24	29,000
11	=DCOUNTA(B1:D10,"Salary",D16:D17) = 9
12	=DCOUNTA(B1:D10,"SALARY",D16:D17) = 9	Name	Age	Salary
13	=DCOUNTA(B1:D10,"Salary",B19:B20) = 9	John	<22	>20000
14	=DCOUNTA(B1:D10,"Salary",B12:D13) = 0	James	>18
15	=DCOUNTA(B1:D10,"Name",C12:C13) = 5
16	=DCOUNTA(B1:D10,"Age",C12) = #VALUE!	Name	Age	Salary
17	=DCOUNTA(B1:D1,"Age",B12:B14) = #VALUE!	James	>18
18	=DCOUNTA(B1:D10,"Salary",B19) = #VALUE!
19	=DCOUNTA(B2:D10,"Age",B12:B13) = #VALUE!
20	=DCOUNTA(B1:D10,,C12:D13) = 2
21	=DCOUNTA(B1:D10,"Age",) = #VALUE!
22	=DCOUNTA(B1:D10,"some text",B12:B13) = #VALUE!

1 - How many people are called "John" with a valid "Age" using a field label.
2 - How many people are called "John" with a valid "Age" using a field number.
3 - How many people are either called "John" or "James" with a valid "Salary".
4 - How many people are less than 22 years old with a valid "Age".
5 - How many people are less than 22 years old or more than 18 years old with a valid "Age". This is basically everyone with a valid "Age".
6 - How many people are less than 22 years old and earn more than £20,000 with a valid "Salary".
7 - How many people earn more than £20,000 with a valid "Age". Matthew is included despite the "Age" column contains text because text is included.
8 - How many people earn more than £20,000 with a valid "Salary".
9 - How many people are called "James" and are more than 18 years old with a valid "Salary".
10 - How many people either have the name "James" are less than 22 years old and earn more than £20,000 or have the name "James" and are older than 18 years old with a valid "Salary".
11 - How many people have a valid "Salary". Putting a blank cell below the column heading will refer to the entire column.
12 - This is the same as 11 but with the field label in uppercase.
13 - If the "criteria" refers to a range of empty cells, then the entire column from the "database" range is used.
14 - If the "criteria" you specify does not return any rows, then 0 is returned.
15 - If the "field" you are trying to count is not numeric in this case "Name", then total number of rows matching the criteria is returned because text is included.
16 - If the "criteria" only includes a column heading, then #VALUE! is returned.
17 - If the "database" range only includes column headings, then #VALUE! is returned.
18 - If the "criteria" refers to a single empty cell, then #VALUE! is returned.
19 - If the "database" range does not include column headings, then #VALUE! is returned.
20 - If "field" is left blank, the total number of rows matching the criteria is returned, including any text values.
21 - If "criteria" is left blank, then #VALUE! is returned.
22 - If "field" does not match one of the column headings, then #VALUE! is returned.