Adding the OR branches
A record may satisfy both criteria rows. The selected database record contributes once, not once per branch.
Summarize records selected by a criteria table
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in N5 and O5. Use the database A1:E13, field header H6 and complete criteria range H1:K3. N5 is the sum of numeric values in the selected field; O5 is their numeric count. Conditions across one criteria row combine with AND; the two rows combine with OR. Repeated Units headers apply both numeric bounds to the same record. Keep each selected record once even when it matches both rows. Keep numeric zeroes and signed amounts; referenced text, numeric-looking text, booleans and blanks do not contribute to either statistic.
Read and edit the applied criteria
All nonblank conditions on one row use AND; the two rows use OR. A blank row matches every record. These controls store literal text, so =East stays a criterion rather than becoming an executable formula.
| Criteria row | Region | Status | Units rule 1 | Units rule 2 |
|---|---|---|---|---|
| 2 | =East | =Posted | >=2 | <=5 |
| 3 | =West | =Released | >=0 | <=9 |
Applied field:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: N5, O5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Read AND/OR criteria from cells and keep the numeric population visible.
THE QUICK ANSWER01
Enter separate formulas in N5 and O5. Use the database A1:E13, field header H6 and complete criteria range H1:K3. N5 is the sum of numeric values in the selected field; O5 is their numeric count. Conditions across one criteria row combine with AND; the two rows combine with OR. Repeated Units headers apply both numeric bounds to the same record. Keep each selected record once even when it matches both rows. Keep numeric zeroes and signed amounts; referenced text, numeric-looking text, booleans and blanks do not contribute to either statistic.
N5
=DSUM($A$1:$E$13,$H$6,$H$1:$K$3)O5
=DCOUNT($A$1:$E$13,$H$6,$H$1:$K$3)For the example data
N5 43.75O5 4
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Criteria are data, not query fragments
Region and Status criteria use text prefixes, wildcard patterns or explicit =exact text. Comparisons are case-insensitive; * matches multiple characters, ? matches one, and ~ escapes a wildcard or tilde. A leading apostrophe is an Excel entry aid for storing a criterion beginning with = as literal text; it is not part of the stored criterion.
Keep records and numbers distinct
A criteria rule selects records first. Numeric summary functions then ignore nonnumeric values in the selected field. A matching record containing text "18" or TRUE is different from a numeric observation, and a unique literal "#NUM!" is different from the actual multiple-match error.
Keep the supported boundary visible
Units inputs are whole numbers within ±10,000. Amount accepts numeric cents within ±1,000,000, literal text, Boolean values or blanks. Numeric comparisons in this course apply only to Units. Text ordering/not-equal criteria, calculated criteria columns, external databases and typed source errors require separate rules.
Read the full step-by-step walkthrough
- Include the database headers
The first row supplies field names. The database range includes all twelve original records, not only the visible matches.
- Read each criteria row as a rule
Across H:K, all nonblank conditions on a row must hold. Across rows 2 and 3, either complete rule can select a record. Matching both rows does not create a second observation.
- Constrain the same Units field twice
The repeated Units headings apply a lower and an upper bound to one record. A completely blank criteria row matches all records.
- Select the result field
H6 is the field header Amount or Units. Keep it as a worksheet reference so the same formulas work when the selected field changes.
- Return the DSUM result
Select numeric observations without coercing text or booleans. Each source record contributes at most once.
- Return the DCOUNT result
Select numeric observations without coercing text or booleans. Each source record contributes at most once.
Things to watch
Treating both rows as AND
All columns on a row combine with AND. Different rows are alternatives.
Returning the first matching record
DGET is an exactly-one-record operation. Returning a first match conceals ambiguity.
About this example
Original criteria records and independent selection/type/cardinality rules checked against the real browser formula engine. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.