© Ugur Akinci
You can use DCOUNT function in MS Excel to count the cells of a table according to a single or multiple criteria.
The general format of the DCOUNT function is as follows:
- Database is the range of the data table (like, A1:H9). Or, you can give it a NAME and refer to it by its name only as well.
- Field is the number of the COLUMN in which you will count the number of cells.
NOTE: With DCOUNT you can count only down a column, but not across a row!
- Criteria is the address of the cells where the criteria is/are (like G5:H7).
Let’s illustrate this with an example.
Imagine you have the following table for car brands, models, and MPG (Mile Per Gallon) ratings:
Here are three different criteria, with the result:
CRITERIA 1 (in cells F2:H3)
Here we wanted to find all cars (cells) that have an MPG greater than 10. Result is 11.
CRITERIA 2 (in cells F8:H9)
Here we wanted to find all cars (cells) that have an MPG greater than 10 AND the letter “C” for the first letter of its model name. We have used TWO conditions. Result is 4.
CRITERIA 3 (in cells F14:H15)
Here we wanted to find ONLY those cars (cells) with the letter “C” for the first letter of their model name. Result is 5.
Your imagination is the only limit when it comes to using this very versatile MS Excel function.
“Can I refer to a subset of the data by name only and still use DCOUNT?”
Yes, you can. Here is an example. We have selected all FORD rows and named it “FORDS“:
Here is a DCOUNT statement that refers specifically to the FORDS sub-table, and the result:
The result is 0 (zero) because there are no Ford models in the sub-table that start with the letter “C”.