DCOUNTA Function

Counts the non-blank cells in a column for the records that match your criteria.

Category

Database

Available in

All versions, Microsoft 365

Returns

A whole number

Want to practice DCOUNTA in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DCOUNTA counts matching records where the field column isn't empty — text, numbers, dates and logical values all count.

Use it on text columns, or to check how many matching records have a value filled in.

Syntax

=DCOUNTA(database, [field], criteria)

Arguments

ArgumentRequired?What it does
databaseYesThe whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field.
fieldOptionalThe column to check for non-blank cells. Leave it empty to count all matching records.
criteriaYesA small range with at least one header copied exactly from the list and the conditions underneath, e.g. G1:G2.

Examples

Example 1 — How many South orders have a rep?

Rep is a text column, so DCOUNTA (not DCOUNT) is the one to use.

G5fx=DCOUNTA(A1:E7,"Rep",G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesRegion
2NorthAmaraPaper40520South
3SouthBenToner12576
4NorthChloeToner8384With a rep
5EastDevPaper253252
6NorthAmaraPens100120
7SouthEllaPaper30390
=DCOUNTA(A1:E7,"Rep",G1:G2) Explain

Example 2 — Products beginning with “P”

Plain text criteria match the start of the text, so P finds Paper and Pens.

G5fx=DCOUNTA(A1:E7,"Product",G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesProduct
2NorthAmaraPaper40520P
3SouthBenToner12576
4NorthChloeToner8384Orders
5EastDevPaper253254
6NorthAmaraPens100120
7SouthEllaPaper30390
=DCOUNTA(A1:E7,"Product",G1:G2) Explain

Common mistakes

  • Cells that look empty still count. A formula returning "" or a space isn't blank to DCOUNTA.

Notes

  • Criteria rules: conditions on the same row must all be true (AND); conditions on separate rows are alternatives (OR). Text such as Nor matches anything that begins with it — type ="=North" for an exact match. Use >500, <>East, or wildcards like *Paper*.
  • Related functions: DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.