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

Engineering test data rarely arrives as a single clean column. A typical qualification run produces a flat table where each row is one measurement: a unit serial number, a test station, a timestamp, a measured value, and a specification limit. The analytical questions, however, are almost always grouped and conditional: what is the mean pull force for lot B on station 3? How many units fell outside the ±5% tolerance band? What is the average insulation resistance for the 85 °C subgroup only? The three *IFS functions answer exactly these questions without sorting, filtering, or pivot tables, and they recalculate automatically when new rows are appended.

The shared design principle is straightforward: each function takes one aggregation range (the values to sum, count, or average) followed by one or more criteria range / criteria pairs. All criteria are combined with logical AND. A row contributes to the result only if it satisfies every pair simultaneously. This AND semantics is the single most important fact to internalize, because it determines how you build multi-dimensional groupings and how you handle OR logic (which requires either multiple formulas added together or a helper column).

Function signatures and criteria semantics

Criteria accept several forms that matter in practice. A plain value such as "Lot-B" or 3 tests equality. A comparison string such as ">=10" or "<>Pass" tests an inequality. A cell reference combined with an operator, written as ">="&F2, makes the threshold dynamic — this is the pattern that turns a static report into a reusable template. Wildcards * and ? work on text criteria, so "ST-*" matches every station whose name begins with ST-. Note that SUMIFS and AVERAGEIFS place the aggregation range first, whereas the legacy single-condition SUMIF/AVERAGEIF place it last; mixing the two argument orders is a common source of silently wrong totals.

Grouped test results: building a summary block

Assume a measurement table on sheet Raw with headers in row 1 and data in A2:F5000: A = UnitID, B = Lot, C = Station, D = Temp_C, E = PullForce_N, F = Result (Pass/Fail). On a summary sheet, list the grouping keys down columns and reference them with mixed anchoring so a single formula can be filled across the whole grid:

=COUNTIFS(Raw!$B$2:$B$5000,$A3, Raw!$C$2:$C$5000,B$2)
=AVERAGEIFS(Raw!$E$2:$E$5000, Raw!$B$2:$B$5000,$A3, Raw!$C$2:$C$5000,B$2)
=SUMIFS(Raw!$E$2:$E$5000, Raw!$B$2:$B$5000,$A3, Raw!$C$2:$C$5000,B$2)

Here $A3 locks the lot key to column A while letting the row advance, and B$2 locks the station key to row 2 while letting the column advance. Filling this 3×3 block produces sample size, mean, and total for every lot–station combination in one operation. Two engineering cautions apply. First, AVERAGEIFS returns #DIV/0! when a group has zero matching rows — wrap it in IFERROR(…,"—") or guard with the companion COUNTIFS so empty combinations do not pollute charts. Second, criteria ranges must all be the same size as the aggregation range; a mismatched height returns #VALUE!, which usually means one range was extended to row 6000 while another stops at 5000.

Tolerance bands as criteria

Tolerance analysis is where these functions earn their place. Suppose the nominal pull force is 120 N with a ±5% band, i.e. 114 N to 126 N. Store the limits in cells — say LSL in H1 and USL in I1 — and compute them from nominal and tolerance so the band updates when the drawing revision changes:

H1: =120*(1-5%)      → 114
I1: =120*(1+5%)      → 126

In tolerance:  =COUNTIFS(Raw!$E$2:$E$5000,">="&$H$1, Raw!$E$2:$E$5000,"<="&$I$1)
Below LSL:     =COUNTIFS(Raw!$E$2:$E$5000,"<"&$H$1)
Above USL:     =COUNTIFS(Raw!$E$2:$E$5000,">"&$I$1)
Yield:         =COUNTIFS(...in-tolerance...)/COUNTIFS(Raw!$E$2:$E$5000,"<>")

The "<>" criterion in the denominator counts non-blank cells, giving a denominator that ignores trailing empty rows. Because the band edges are inclusive here, a reading of exactly 114.0 N counts as in-tolerance; state this convention explicitly in the report, since switching to exclusive bounds changes the yield figure and must be applied consistently across every formula and chart. To break the out-of-tolerance count down by lot, add the lot criterion pair to the same formula rather than creating separate formulas per lot.

Combining conditions and handling OR logic

Because all criteria are ANDed, an OR condition must be expressed as a sum of separate calls. Counting units that failed either the pull-force band or the resistance limit becomes:

=COUNTIFS(Raw!$E$2:$E$5000,"<"&$H$1)
 +COUNTIFS(Raw!$E$2:$E$5000,">"&$I$1)
 +COUNTIFS(Raw!$G$2:$G$5000,">"&$J$1)
 -COUNTIFS(Raw!$E$2:$E$5000,"<"&$H$1, Raw!$G$2:$G$5000,">"&$J$1)
 -COUNTIFS(Raw!$E$2:$E$5000,">"&$I$1, Raw!$G$2:$G$5000,">"&$J$1)

This is inclusion–exclusion: add each single-condition count, then subtract the pairwise intersections that were double-counted. It is correct but fragile as conditions multiply. When three or more OR branches are needed, a helper column is cleaner and auditable — for example =--OR(E2<$H$1, E2>$I$1, G2>$J$1) producing 1/0 flags, then a single SUMIFS over the flag column with the lot and station criteria. The helper column also makes the logic visible to reviewers, which matters in regulated test reporting.

Reading the results and catching typical failures

A correct summary block should satisfy internal consistency checks before anyone interprets it. The in-tolerance count plus the below-LSL count plus the above-USL count must equal the non-blank total; if it does not, a boundary is exclusive where it should be inclusive, or a range is misaligned. The group means weighted by their COUNTIFS sample sizes should reproduce the overall mean computed by a single AVERAGEIFS with no grouping criteria — a mismatch signals rows whose lot or station key is blank or misspelled. Text criteria are case-insensitive and ignore leading/trailing spaces only if the data is clean, so trailing spaces in a lot code will silently create a phantom group with a small count; TRIM the key columns in the source or in a staging sheet.

The figure above traces the same logic the formulas implement: rows enter from the raw sheet, pass through the AND-combined criteria block, and fan out to the three aggregators, while the OR branch is handled separately by inclusion–exclusion or a flag column. Once this summary block is in place, appending new measurements to the raw sheet requires no formula edits — only the named ranges need to cover the new rows — and the grouped means, sample sizes, and tolerance counts update together, giving a reproducible yield and capability view that can be refreshed after every test run.