**DCOUNT** in Excel is a handy formula that can count items based on a set of criteria. The criteria can be quite general, including matching words, specifying numerical bounds, and many other possibilities.

DCOUNT is basically a more powerful version of the COUNTIF function. (Check out: How to Use the COUNTIF Function in Excel for more details.)

In the following article, we’ll see a number of ways in which DCOUNT can be used to count data using specific criteria.

## DCOUNT Syntax

First of all, let’s describe the syntax.

=DCOUNT( *data*, *[field]*, *criteria* )

*data*– the range of cells containing the raw data. Be sure to include a header row that labels the data as this will be needed to match up with headers in*criteria*.*field*– an optional parameter that specifies which column of items to count. This only makes a difference if the*field*column has blank entries and you want to count only rows with data in that field.*criteria*– the range of cells containing the comparisons that you want to make. The first row should have headers that match those of the*data*, although you do not have to match every header, and you can even repeat headers!

## Example: What’s my Profit?

Perhaps we own a restaurant and we need to keep track of day-to-day costs, revenue, and profit. On some days, we may offer a Special dish, so let’s record that as well.

The screenshot shows an array of **Data**, with headers Day, Costs, Revenue, Profit, and Special. Cell E5 already contains the DCOUNT function with the *data* range (A8:E18) and *criteria* range (A2:E3) correctly identified. We’ve left *field* blank for now because we’re just interested in the data set as a whole.

Suppose you want to count the number of rows in which the costs were less than $1500, and the profit was greater than $0 but less than $100.

Simply type “< 1500” in cell B3, “> 0” in cell C3, and “< 100” in cell D3. Notice that B3 is below the header "Costs," so Excel will test whether Costs < 1500 in the Data. Similarly, both C3 and D3 have "Profit" as header. If you specify multiple criteria under the same header, then all criteria must be met in order to be counted. Thus, only data with 0 < Profit < 100 will be counted.

## Counting the Days

Let’s another criterion to illustrate how text-matching works. Suppose you wanted to know how many *Fridays* your restaurant had less than $1500 in Costs and between $0 and $100 in Profit.

Type the following in cell A3 (right under *Day* in the Criteria section):

=”=Friday”

Notice the added layer of complication? Basically, you need to tell Excel “I want the contents of the cell to begin with “=”, but I don’t want that equals sign to turn it into a formula. So how do you do that? Strangely enough, you embed it into another formula!

Now you’ll see that DCOUNT returns 1 because there is only one row that meets all of the criteria.

## DCOUNT vs. DCOUNTA

Be careful when you specify the optional *field* argument. You may get some unexpected results!

For example, if you wanted to narrow down the scope of the search to only rows having “Yes” in the Special column, it might make sense to set *field* to “Special.” However, doing so would result in a count of 0.

The problem is that DCOUNT will only count numerical data in the column specified by *field*. To count nonblank text items, use DCOUNTA. The syntax is exactly the same:

=DCOUNTA( *data*, *[field]*, *criteria* )

So let’s try this in our example. Edit cell E5 so that it now contains:

=DCOUNTA(A8:E18, “Special”, A2:E3)

This time, the count should be 1. There is only a single row in which Costs < 1500, 0 < Profit < 100, *and* the Special was offered.

## Summary

That’s all there is to it! Using DCOUNT (or DCOUNTA) allows you to count items in a range of data based on specified criteria.

Now let’s get back to the restaurant, and see if we can’t turn a profit. I think that new Special is driving away customers!

## Comments are closed.