Conditional Aggregation
Count Unique Rows or Columns Containing a Specific Value (Avoiding Double-Counts)
Count how many distinct rows or distinct columns in a 2D inspection matrix contain at least one "Fail" without double-counting multiple failures on the same row or station!
Why COUNTIF(B2:D4, "Fail") overcounts defective batches when one batch fails two stations!
In a 3-batch × 3-station QA inspection matrix, Batch 1 fails BOTH Station 1 and Station 3, while Batch 3 fails Station 2. Naive COUNTIF(B2:D4, "Fail") reports 3 cell failures—wrongly implying all 3 batches failed! To count defective ROWS (batches with >= 1 failure) vs. affected COLUMNS (stations with >= 1 failure), clamp each row’s and each column’s COUNTIF with --(COUNTIF(...) > 0) before summing!
Row indicator E2 = --(COUNTIF(B2:D2, $G$2) > 0) converts [2, 0, 1] cell hits into [1, 0, 1] -> SUM(E2:E4) = 2 defective batches, while column indicator row B5:D5 = --(COUNTIF(B2:B4, $G$2) > 0) yields SUM(B5:D5) = 3 affected stations!
| QA Matrix Dimension (Target G2 = "Fail") | Raw Cell Hits | Clamped --(COUNTIF > 0) Vector | Distinct Count |
|---|---|---|---|
| Defective Batches (Rows 2–4) | [2, 0, 1] failures/row | [1, 0, 1] | 2 distinct rows |
| Affected Stations (Cols B–D) | [1, 1, 1] failures/col | [1, 1, 1] | 3 distinct columns |
| Target G2 = "Hold" (Absent) | [0, 0, 0] | [0, 0, 0] | 0 rows / 0 columns |
Count Distinct Matrix Rows Containing Target Value G2
Sums binary row-presence flags E2:E4 = --(COUNTIF(B2:D2, $G$2) > 0) so multiple hits in the same row count once.
=SUM(E2:E4)Count Distinct Matrix Columns Containing Target Value G2
Sums binary column-presence flags B5:D5 = --(COUNTIF(B2:B4, $G$2) > 0) across stations.
=SUM(B5:D5)Try the example
Use the original values or change them to test your understanding.