4. Conditional aggregation with SUMIFS, COUNTIFS, and AVERAGEIFS for grouped test results and tolerance compliance counts.

Engineering test data rarely arrives as a single homogeneous block. A qualification campaign typically produces one row per test point, with columns for the unit under test, the test condition (temperature, voltage, load), the measured value, and a pass/fail verdict. The analytical questions that follow are almost always grouped questions: what is the mean insertion loss across all units at 85 °C? How many samples exceeded the tolerance band at nominal voltage? What is the average settling time for the revision B boards only? The SUMIFS, COUNTIFS, and AVERAGEIFS family answers exactly these questions without sorting, filtering, or pivot tables, and — critically — without breaking when new rows are appended.

All three functions share the same argument grammar: one aggregation range followed by one or more criteria_range / criteria pairs. Every criteria range must have the same height and width as the aggregation range, otherwise Excel returns #VALUE!. Criteria are combined with implicit AND logic, so each additional pair narrows the result set. This is the single most important mental model: these functions compute over the intersection of all supplied conditions.

Building a grouped summary from a flat test log

Consider a thermal cycling log with columns A: UnitID, B: Chamber, C: Temp_C, D: Cycle, E: Rds_on_mOhm, and F: Verdict (values PASS / FAIL), rows 2 through 5001. A grouped summary table is placed to the right, with chamber names in H2:H5 and temperature setpoints in I1:M1 (e.g., −40, 25, 85, 125). The mean on-resistance for each chamber/temperature cell is:

=AVERAGEIFS($E$2:$E$5001,
             $B$2:$B$5001,$H2,
             $C$2:$C$5001,I$1)

Filling this across and down produces a two-dimensional summary in a single formula pattern. Note the mixed referencing: $H2 locks the column so the chamber criterion travels down the rows, while I$1 locks the row so the temperature criterion travels across the columns. Getting this anchoring wrong is the most common cause of a summary table that looks plausible but is silently wrong in half its cells — always spot-check one interior cell by manually filtering the source data.

Counting failures per chamber uses the same skeleton with COUNTIFS and no aggregation range:

=COUNTIFS($B$2:$B$5001,$H2,
           $F$2:$F$5001,"FAIL")

Because COUNTIFS has no leading sum range, every argument is a criteria pair. The total sample count per chamber is the same formula with "FAIL" replaced by "<>" (not equal to blank), and the yield is simply the pass count divided by the total — a ratio that stays correct as the log grows.

Tolerance compliance counts

Tolerance compliance is where conditional counting earns its keep, because the criteria are numeric inequalities rather than labels. If the specification for Rds_on_mOhm is 12.0 mΩ maximum, the out-of-tolerance count is:

=COUNTIFS($E$2:$E$5001,">12",
           $B$2:$B$5001,"Chamber-2")

Two subtleties matter here. First, the comparison operator must be embedded in a quoted string; writing >12 unquoted is a syntax error, and writing ">"&12 is the correct way to concatenate a threshold held in a cell (e.g., ">"&$J$1). Second, the boundary convention must be stated explicitly and applied consistently: ">12" excludes exactly 12.0, whereas ">=12" includes it. For a maximum-limit specification, the compliant set is <=12, so the non-compliant count is ">12". Mixing these up shifts the count by the number of units sitting exactly on the limit — often a small but audit-critical number.

A tolerance band (two-sided specification) requires two criteria on the same range, which is perfectly legal:

=COUNTIFS($E$2:$E$5001,">=9.5",
           $E$2:$E$5001,"<=12",
           $C$2:$C$5001,85)

This counts in-band units at 85 °C. The complement — units outside the band — is the total at that temperature minus the in-band count, which is more robust than writing two separate "<9.5" and ">12" counts and adding them, because the complement approach cannot double-count or miss a boundary value.

Reading the aggregation logic as a filter chain

The diagram below shows how a single AVERAGEIFS call resolves: each criteria pair acts as a mask over the same row index space, the masks intersect, and only the surviving rows contribute to the aggregation range. This is the structure to keep in mind when a result looks wrong — the question is always which mask is too tight or too loose.

Diagnosing the three failure modes

#DIV/0! from AVERAGEIFS means the intersection is empty — no row satisfies every criterion. This is not an error in the formula but a signal about the data or the criteria. Check for trailing spaces in text criteria ("Chamber-2 " will not match "Chamber-2"), numeric criteria stored as text, and temperature values recorded as 85.0 versus 85. A quick diagnostic is to replace AVERAGEIFS with COUNTIFS using the identical criteria pairs; a count of zero confirms the empty intersection before you touch the aggregation.

#VALUE! from mismatched ranges occurs when a criteria range is a different size from the aggregation range — for example, $E$2:$E$5001 paired with $B$2:$B$5000. Because these ranges are usually written once and filled, a single off-by-one in the original formula propagates silently across the whole summary. Define the ranges once as named ranges (Rds, Chamber, Temp, Verdict) and reference the names; Excel then enforces consistent sizing and the formulas become readable.

Plausible but wrong values are the dangerous case. The usual culprits are relative references that shift when filled (fix with $), boundary operators that disagree with the specification convention, and criteria that match more than intended — for instance "Chamber-1" also matching "Chamber-10" if wildcards or partial matching are in play. Excel's criteria matching for text is exact by default, so this specific trap arises mainly when wildcards are used deliberately; still, verify with a filtered count.

A compact compliance summary worth keeping

The following layout is a practical deliverable for a qualification report. It assumes the log described above and a specification band of 9.5–12.0 mΩ. Each cell is a single formula; the table is filled from one anchor cell.

Two conventions in this table deserve emphasis because they determine whether the report survives review. First, the out-of-band count is derived by subtraction rather than by summing "<9.5" and ">12"; this guarantees that in-band plus out-of-band equals the sample count exactly, which is the first thing an auditor checks. Second, the pass-only mean uses Verdict,"PASS" as an explicit criterion rather than relying on failed units being absent from the data — failed units are present, and including them would bias the mean upward in exactly the direction that flatters the design.

Once these patterns are in place, extending the analysis is mechanical: add a criteria pair for lot number, test station, or operator, and the same formulas segment the data without restructuring it. The discipline that makes this reliable is not formula cleverness but consistency — one stated boundary convention, one set of named ranges, and a complement-based check that every subgroup's counts reconcile to its total.