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 |
|
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. |
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top