Read the criteria table before calculating

Build one record predicate per row

Columns on the same criteria row combine with AND. Different criteria rows are alternatives. Repeating a Units header lets one row state a lower and an upper bound on that same field. A record that satisfies two alternatives is selected once.

A completely blank criteria row selects every database record. Leaving an unused-looking row inside the referenced criteria range therefore changes the question.

Keep text matching intentional

Plain text such as Ea is a prefix criterion. =East asks for the exact text, without case sensitivity. An asterisk matches multiple characters, a question mark matches one, and a tilde escapes an asterisk, question mark or tilde. Use =A~*B for the literal label A*B.

A criterion beginning with = must remain text in the worksheet. When entering it manually in Excel, a leading apostrophe stores text; the apostrophe is not part of the resulting criterion value. The original workbooks store these as text cells.

Choose what happens after selection

DSUM and DCOUNT use numeric values in the selected field. Numeric zeroes and negative observations count; literal numeric text and booleans are different source types. DAVERAGE uses the same numeric denominator, while DMIN and DMAX inspect its extremes.

DGET checks the number of selected records, not the number of distinct result values. Two records with identical amounts are still ambiguous. No record produces #VALUE!, multiple records produce #NUM!, and one record returns its actual field value. A literal error-looking string remains text.

Try the idea

• Summarize selected records →
• Retrieve exactly one record →
• Describe numeric selections →

References

• Microsoft database criteria examples ↗
• Microsoft DGET cardinality ↗

Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.