Statistical & Clinical Analysis
Average & Sum If Companion Column Is Not Blank (and Exclude Zero Placeholders) with AVERAGEIFS
Average or sum values in column C only where companion column B is populated ("<>") while optionally excluding unrecorded 0 placeholders ("<>0") without triggering #DIV/0!.
AVERAGE(C2:C6) already ignores blanks in C—so when DO you need "Average If Not Blank"?
Two distinct real-world traps drive searches for "Average If Not Blank": (1) Column C holds pre-filled numbers or formulas, and you ONLY want to average rows where a companion column B (such as Inspection_Date or Reviewer_ID) is NOT blank ("<>")! (2) Exported ERP feeds fill missing measurements in C2:C6 with numeric 0 placeholders instead of true blanks, dragging AVERAGE(C2:C6) down artificially!
AVERAGEIFS(C2:C6, B2:B6, "<>", C2:C6, "<>0") solves both problems simultaneously—averaging C2:C6 strictly across rows where companion cell B is non-empty AND C is non-zero! Always wrap with a COUNTIFS(..., B2:B6, "<>", C2:C6, "<>0") = 0 pre-check so an uninspected sheet returns a clean status label instead of #DIV/0!.
| Aggregation Goal on Values C2:C6 & Reviewer B2:B6 | Worksheet Formula | Result on Sample Data |
|---|---|---|
| Naive AVERAGE(C2:C6) (Includes unreviewed Row 4 & 0 placeholder Row 5!) | =AVERAGE(C2:C6) | 64.00 (Severely diluted!) |
| Sum Only Reviewed Rows (B2:B6 Not Blank "<>") | =SUMIFS(C2:C6, B2:B6, "<>") | 240.00 (Rows 2, 3, 5, 6) |
| Average Reviewed & Non-Zero Rows Only | =AVERAGEIFS(C2:C6, B2:B6, "<>", C2:C6, "<>0") | 80.00 (Rows 2, 3, 6: [78, 84, 78]) |
Guarded Average If Companion Column Not Blank & Non-Zero
Computes the mean of C2:C6 across rows with a non-blank Reviewer in B2:B6 and non-zero score in C2:C6.
=IF(COUNTIFS(B2:B6, "<>", C2:C6, "<>0") = 0, "No Reviewed Non-Zero Rows", ROUND(AVERAGEIFS(C2:C6, B2:B6, "<>", C2:C6, "<>0"), 2))Sum If Companion Column Not Blank =SUMIFS(C2:C6, B2:B6, "<>")
Totals C2:C6 strictly across rows where companion column B2:B6 contains a value.
=ROUND(SUMIFS(C2:C6, B2:B6, "<>"), 2)Try the example
Use the original values or change them to test your understanding.