DCOUNTA in Excel: Nonblank Fields and Database Completeness

=DCOUNTA(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. Database and criteria are required. Field is optional for DCOUNTA; omit its position to count matching records.

Enter this mixed-value database in A1:D8:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. Type pending as ordinary text in C8 and leave D8 physically empty, rather than typing the instruction. F1:G2 selects four North/A records. Three Units values are numeric (2, 4 and 6), and the fourth is text; three Sales values are numeric, and the fourth is empty.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

=DCOUNTA(A1:D8,"Units",F1:G2)
=DCOUNTA(A1:D8,"Sales",F1:G2)

Expected counts are 4 and 3. Pending is text but is not blank. The empty Sales cell has no entered value. These counts describe presence, not numerical validity.

For the selected Units field, DCOUNT instead returns 3, counting only numeric observations. Subtracting DCOUNT from DCOUNTA gives one nonnumeric populated entry in this controlled fixture.

=DCOUNTA(A1:D8,,F1:G2)

Expected result: 4. With field omitted, the dedicated reference says DCOUNTA counts all matching records. Leave the second argument position empty so criteria remains the third argument.

This omitted-field count is not tied to the completeness of Units or Sales. It gives a denominator for a completeness report even when one particular field is empty.

For the four matching records, Units presence is 4/4 = 100%, while numeric Units coverage is 3/4 = 75%. Sales presence and numeric coverage are both 3/4 = 75% in this dataset.

A report saying only “100% complete” for Units would hide the pending status. Keep the metric’s definition visible: nonblank presence and numeric measurement availability are different checks.

These are audit indicators, not proofs of correctness. A populated number can still have the wrong unit, an impossible magnitude or the wrong record association.

In a separate copy, enter numeric 0 in D8. DCOUNTA on Sales now has expected count 4. DCOUNT also rises to four because zero is numeric. The Sales total remains 600, but a mean over all four numeric observations becomes 150 instead of 200.

Do not fill an empty field merely to improve a completeness percentage. Doing so changes downstream statistics even when a total is unaffected.

Put Team in J1 and ="=North" in J2. North has five matching records, including Product B. DCOUNTA on Units has expected 5; DCOUNT on Units has expected 4; DCOUNTA on Sales has expected 4.

The separate Product criterion determines which records are included. Retain both counts and the exact criteria range in an audit trail so changes in coverage can be explained by data changes or selection changes.

The fixture uses a physically empty cell, not a formula returning an empty string, a space or an error. Those values can behave differently from a blank and are not certified by this example. Inspect the actual cell contents before treating a visually empty display as missing.

The dedicated reference does not establish every malformed-field or invalid-range error code. Do not suppress such errors and report them as zero populated entries.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DCOUNTA reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.