Using DCOUNT in Excel

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.

Screenshot of an example that uses Dcount to keep track of data based on various criteria

Screenshot of an example that uses DCOUNT to keep track of data based on various criteria. Cell E5 shows the result: there are five rows that show a positive profit.

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.

There are two rows in which Costs < 1500 and 0 < Profit < 100

There are two rows in which Costs < 1500 and 0 < Profit < 100 (highlighted by hand. DCOUNT only counts rows but does not indicate which rows satisfy the criteria.)

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!

showing DCOUNT with text criteria

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!

Calamari

Not everyone is a fan of calamari. Source: pixabay.com

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!