Data Validation & Auditing
Count Positive, Negative, Numeric & Formula-Error Cells in a Ledger Range
Reconcile mixed audit columns containing gains, losses, zeros, text notes, and #DIV/0! errors using COUNTIF(">0"), COUNTIF("<0"), COUNT, and --ISERROR(B2) flags.
Decomposing a messy trial-balance column into positive, negative, zero, text, and error counts!
When auditing an imported variance column B2:B8 that mixes positive gains, negative chargebacks, exact 0.00 break-even rows, text notes ("Pending"), and broken formula cells (=1/0 -> #DIV/0!), SUM(B2:B8) fails immediately with #DIV/0!! Sanitizing numeric values into D2 = IF(ISNUMBER(B2), B2, 0) while tracking errors in C2 = --ISERROR(B2) lets every reconciliation formula run cleanly without #DIV/0! propagation!
Combining COUNTIF(D2:D8, ">0") for strictly positive cells, COUNTIF(D2:D8, "<0") for strictly negative cells, COUNT(B2:B8) for all valid numbers (positive + negative + zero), and SUM(C2:C8) for formula errors gives a complete reconciliation before close!
| Audit Bucket Across Rows 2–8 (7 Rows) | Exact Formula | Count in Sample Ledger | What Gets Excluded |
|---|---|---|---|
| Strictly Positive (> 0) | COUNTIF(D2:D8, ">0") | 2 rows (420, 185) | Zeros, negatives, text, #DIV/0! |
| Strictly Negative (< 0) | COUNTIF(D2:D8, "<0") | 2 rows (-110, -45) | Zeros, positives, text, #DIV/0! |
| All Valid Numbers (Incl. 0) | COUNT(B2:B8) | 5 numeric rows | Text "Pending" & #DIV/0! errors |
| Formula Errors (#DIV/0!, #N/A) | SUM(C2:C8) via --ISERROR(B2) | 1 error row (B7 = 1/0) | Numbers, zeros, text |
Count Strictly Negative (< 0) & Strictly Positive (> 0) Cells
Counts negative entries across ISNUMBER-sanitized column D2:D8 without choking on text strings or #DIV/0! error cells.
=COUNTIF(D2:D8, "<0")Count Formula Error Cells Across Audit Helper Column C2:C8
Sums binary --ISERROR(B2) flags C2:C8 so every #DIV/0!, #VALUE!, or #N/A cell is tallied cleanly.
=SUM(C2:C8)Try the example
Use the original values or change them to test your understanding.