I have a table that contains 21 bushels of apples, each with a different quantity. I want to know how many fall within a certain count range. Let’s see how I have things set up:
I want to know how many of these bushels have between 250 and 1,000 apples. I’ll put those figures in the criteria cells:
Next, I will enter into cells E4 & F4 the criteria formulas to define my requirements. The format for this is:
– start with an = sign as all formulas do
– within quotes, enter the criteria – greater than or equal to (“>=”) and less than or equal to (“<=”)
– insert an “&” which is the connector symbol
– then enter the cell reference from the criteria box – cells E7 & F7
Here’s what that will look like:
Now that you have your table and have set up your criteria, it’s time to enter the DCOUNT formula in the Qty cell (E10) to calculate the count that meet your criteria. The format of DCOUNT is:
database = you table of bushels including the header = B3:C24
field = the name of the field in the table you want to count = Qty of Apples = E3
Criteria = the formulas that define the range, including the headers = E3:F4
And the answer to the query is 9 bushels that have a count between 250 & 1,000. Since we structured the worksheet to allow you to easily vary the criteria, all you need do is change the numbers in cells E7 & F7 and it will adjust the DCOUNT formula accordingly.
This could have a wide variety of uses. Can you think of a few in your daily work? If you have a fixed range that you want to query, instead of creating the range entry boxes in cells E6:F7, you could just enter the values into the formulas in E4 & F4 like this: E4 >=250 F4 <=1000. This will result in the same answer, but give you less flexibility if you need to vary the criteria.