Counting Records With DCOUNT

DCOUNT counts how many records in a table match your criteria range - but with a twist: it only counts rows where the chosen field contains a number.

It is the database-function cousin of COUNT. If you need to count rows by text or blank fields, you reach for DCOUNTA instead, which we will meet shortly.

DCOUNT uses the same three arguments as every D-function:

DCOUNT(database, field, criteria)

• database - the full table with headers.
• field - the column whose numeric values are checked.
• criteria - the matching rules block.

It returns how many matching rows have a number in that field.

Using sales data in A1:C13 with headers Region, Rep, Amount: put Region in E1 and East in E2.

The formula counts East rows that have a numeric Amount. If there are four East orders all with amounts, the result is 4. Change the field to a non-numeric column and the count behaves differently, as we will see.

DCOUNT counts only cells that hold numbers in the field column. If you point it at the Rep column, which holds names, it returns 0 even when rows match the criteria - because names are not numbers.

So choose a numeric field like Amount when counting with DCOUNT. The criteria still filters the rows; the field just decides what counts as countable.

What if you want to count all matching rows regardless of which fields are numeric? You can often omit the field by passing an empty pair of quotes or a header that always has data.

A reliable habit is to point DCOUNT at a column you know is always filled with numbers, such as an ID or Amount. That way the count equals the number of matching records.

DCOUNTA counts matching rows where the field is not blank - numbers and text both count. Use it when your field is text, like Rep.

To count how many East rows have a salesperson recorded, put Region/East in the criteria and use the Rep field. DCOUNT would return 0 here; DCOUNTA correctly counts the non-empty names.

Combine DCOUNT with a numeric criterion to answer 'how many large orders are there?' Put Amount in E1 and >1000 in E2.

Now the formula counts every row whose Amount exceeds 1000. Because the field and the criteria column are both Amount, the count equals the number of big orders.

Count East orders over 1000 by using a two-column criteria range: headers Region and Amount in E1:F1, then East and >1000 in E2:F2.

Same-row conditions mean AND, so DCOUNT counts rows that are East and above 1000. The field stays Amount so each qualifying row contributes one to the count.

To count East or West orders, stack values on separate rows: Region in E1, East in E2, West in E3.

Separate rows mean OR, so DCOUNT counts any row matching either region. Extend the criteria argument to E1:E3 so both condition rows are included.

Like DSUM, DCOUNT trades inline criteria for a cell-based criteria range:

• COUNTIFS is concise for fixed, simple conditions.
• DCOUNT lets users change filters by editing cells and expresses OR logic with extra rows instead of stacked formulas.

Remember DCOUNT needs a numeric field; the COUNTIFS equivalent below counts matching rows directly.

DCOUNT and DSUM make a great team on a dashboard. Use the same criteria range for both: DSUM gives the total and DCOUNT gives how many records made it up.

Together they let you show an average without DAVERAGE, or simply report 'East had 4 orders totaling 2500'. Because both read the same criteria cells, changing one filter updates the count and the total in sync.

You point DCOUNT at the Rep column, which holds salesperson names, with criteria that match several rows. What happens?

DCOUNT counts matching records where the field is numeric:

• Syntax: DCOUNT(database, field, criteria).
• It counts only numbers in the field - use DCOUNTA for text or any non-blank value.
• Same-row criteria = AND; separate rows = OR.
• Point the field at a column that always has numbers to count every matching row.

Next we average matching records with DAVERAGE and pull single values with DGET.