Setting Up a Criteria Range

Excel has a family of database functions whose names all start with the letter D: DSUM, DCOUNT, DAVERAGE, DGET, and more.

They work on a table laid out like a tiny database: a row of column headers on top, with records below. Instead of typing conditions inside the formula, you point each D-function at a separate criteria range on the sheet that describes what to match.

This lesson is all about building that criteria range correctly, because every D-function depends on it.

Every D-function shares the same three arguments:

• database - the whole table including its header row.
• field - which column to operate on (a header name in quotes, or a column number).
• criteria - the range that holds your matching rules.

So the shape is always DSUM(database, field, criteria). The criteria argument is the part beginners get wrong, so we focus on it first.

A criteria range is just a small block of cells with at least two rows:

• The top row holds column headers that match the database headers exactly.
• The rows below hold the conditions.

Imagine sales data with headers Region, Rep, Amount. To match only the East region, your criteria range is two cells stacked: Region on top, East below it.

The header in your criteria range must spell the database header identically. If your data column is named Amount but your criteria says Amounts or amt, the D-function will not find the column and may error or return zero.

The safest habit is to copy the header cell from the table and paste it into the criteria range. That guarantees an exact text match including any trailing spaces.

Say A1:C13 holds your table with headers Region, Rep, Amount. In cell E1 type Region and in E2 type East. That two-cell block, E1:E2, is your criteria range.

Now DSUM totals only East rows. The function reads the header in E1, sees it matches the Region column, then keeps only rows where Region equals East.

By default a text condition like East matches values that begin with that text. So East would also match Eastern.

To force an exact match, wrap it with a formula-style comparison: ="=East" typed into the criteria cell. You can also use wildcards: E* matches anything starting with E, and ?at matches Cat, Bat, or Hat.

Criteria are not limited to text. You can use comparison operators for numbers:

• >1000 matches amounts above 1000.
• <=50 matches values 50 or less.
• <>0 matches anything that is not zero.

Put the header (for example Amount) on top and the comparison text beneath it. The D-function evaluates each record's value against that rule.

Conditions placed side by side on the same row are joined with AND - all must be true.

To match East region with Amount over 1000, build a two-column criteria range: headers Region and Amount on the top row, then East and >1000 on the row below. A record passes only if it is East and above 1000.

Conditions placed on separate rows are joined with OR - any matching row qualifies.

To match East or West, put the header Region on top, then East in the next row and West in the row after. The criteria range now spans three rows, and a record passes if it matches either value.

Remember to include all those rows in the criteria argument.

You can combine both layouts. Suppose you want (East AND >1000) OR (West AND >500).

Use two columns, Region and Amount. Put East with >1000 on one row, then West with >500 on the next row. Each row is an AND group; the separate rows act as OR. This grid layout is how D-functions express rich logic without nesting functions.

Watch out for these traps:

• Forgetting the header row - a criteria range needs headers, not just conditions.
• Selecting a blank row inside the criteria range - an empty condition row matches every record, returning everything.
• Header typos that no longer match the table.
• Leaving the criteria block touching the data table so they overlap.

Keep the criteria range in its own clear area of the sheet.

In a D-function criteria range, how are two conditions written on the same row combined?

A criteria range is the heart of every D-function. Key points:

• It needs a header row whose names match the table exactly.
• Conditions go in the rows beneath - text, wildcards, or comparisons like >1000.
• Same row = AND, separate rows = OR.
• Avoid blank condition rows, which match everything.

With a clean criteria range ready, you can now feed it to DSUM, DCOUNT, DAVERAGE, and DGET in the lessons ahead.