| | | This is identical to the DCOUNT() function except that the text, logical values and errors ARE INCLUDED. |
| | | 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. |
| | | This function will only include text, logical values and errors when the cells are counted once the criteria has been matched. |
| | | Example 1 - How many people are called "John" with a valid "Age" using a field label. |
| | | Example 2 - How many people are called "John" with a valid "Age" using a field number. |
| | | Example 3 - How many people are either called "John" or "James" with a valid "Salary". |
| | | Example 4 - How many people are less than 22 years old with a valid "Age". |
| | | Example 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". |
| | | Example 6 - How many people are less than 22 years old and earn more than £20,000 with a valid "Salary". |
| | | Example 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. |
| | | Example 8 - How many people earn more than £20,000 with a valid "Salary". |
| | | Example 9 - How many people are called "James" and are more than 18 years old with a valid "Salary". |
| | | Example 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". |
| | | Example 11 - How many people have a valid "Salary". Putting a blank cell below the column heading will refer to the entire column. |
| | | Example 12 - This is the same as Example 11 but with the field label in uppercase. |
| | | Example 13 - If the "criteria" refers to a range of empty cells, then the entire column from the "database" range is used. |
| | | Example 14 - If the "criteria" you specify does not return any rows, then 0 is returned. |
| | | Example 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. |
| | | Example 16 - If the "criteria" only includes a column heading, then #VALUE! is returned. |
| | | Example 17 - If the "database" range only includes column headings, then #VALUE! is returned. |
| | | Example 18 - If the "criteria" refers to a single empty cell, then #VALUE! is returned. |
| | | Example 19 - If the "database" range does not include column headings, then #VALUE! is returned. |
| | | Example 20 - If "field" is left blank, the total number of rows matching the criteria is returned, including any text values. |
| | | Example 21 - If "criteria" is left blank, then #VALUE! is returned. |
| | | Example 22 - If "field" does not match one of the column headings, then #VALUE! is returned. |
| | | For more examples of how to specify your database criteria, please 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. |
| | | | 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! | | | |
| |
