DCOUNTA

Microsoft publishes this example as '=DCOUNTA(A6:E12, "Profit", A3:F4)' with the result 1 and the explanation "Counts the rows (1) containing 'Apple' in column A with a height >10 and <16. Only row 8 satisfies these three conditions." Criteria A3:F4 is the header row plus the single condition row (Apple, Height >10, Height <16), which selects only the Apple/14ft record; its Profit cell is non-blank, so the count is 1. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents: "The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria." The same criteria as the documented example match exactly one record, so the count is 1 with or without the field. This case exists because an engine that treats the middle argument as required fails here while passing the documented example. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Derived from the documented description, "Counts the nonblank cells in a field (column) of records" -- unlike DCOUNT, which the sibling page defines as counting cells "that contain numbers". Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. All five have a non-blank Tree cell, so DCOUNTA returns 5 where DCOUNT on the same field would return 0. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents the field argument as accepting "a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on". Column 4 of A6:E12 is Yield. Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Each has a non-blank Yield, so the count is 5 -- the same answer as the label form, which is the point of the case. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft publishes this example as '=DCOUNTA(A6:E12, "Profit", A3:F4)' with the result 1 and the explanation "Counts the rows (1) containing 'Apple' in column A with a height >10 and <16. Only row 8 satisfies these three conditions." Criteria A3:F4 is the header row plus the single condition row (Apple, Height >10, Height <16), which selects only the Apple/14ft record; its Profit cell is non-blank, so the count is 1. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents: "The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria." The same criteria as the documented example match exactly one record, so the count is 1 with or without the field. This case exists because an engine that treats the middle argument as required fails here while passing the documented example. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Derived from the documented description, "Counts the nonblank cells in a field (column) of records" -- unlike DCOUNT, which the sibling page defines as counting cells "that contain numbers". Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. All five have a non-blank Tree cell, so DCOUNTA returns 5 where DCOUNT on the same field would return 0. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents the field argument as accepting "a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on". Column 4 of A6:E12 is Yield. Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Each has a non-blank Yield, so the count is 5 -- the same answer as the label form, which is the point of the case. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft publishes this example as '=DCOUNTA(A6:E12, "Profit", A3:F4)' with the result 1 and the explanation "Counts the rows (1) containing 'Apple' in column A with a height >10 and <16. Only row 8 satisfies these three conditions." Criteria A3:F4 is the header row plus the single condition row (Apple, Height >10, Height <16), which selects only the Apple/14ft record; its Profit cell is non-blank, so the count is 1. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents: "The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria." The same criteria as the documented example match exactly one record, so the count is 1 with or without the field. This case exists because an engine that treats the middle argument as required fails here while passing the documented example. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Derived from the documented description, "Counts the nonblank cells in a field (column) of records" -- unlike DCOUNT, which the sibling page defines as counting cells "that contain numbers". Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. All five have a non-blank Tree cell, so DCOUNTA returns 5 where DCOUNT on the same field would return 0. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Microsoft documents the field argument as accepting "a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on". Column 4 of A6:E12 is Yield. Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Each has a non-blank Yield, so the count is 5 -- the same answer as the label form, which is the point of the case. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".