Auditing & Collaboration
Sum Formula-Driven Cells Only While Isolating Hardcoded Manual Overrides (ISFORMULA)
Separate formula-calculated subtotals from hardcoded manual number overrides using ISFORMULA and SUMIF so shared financial models stay 100% auditable.
Catching silent manual overrides in shared budget models
One of the costliest spreadsheet risks occurs when a reviewer overwrites a live formula cell (=B2 * C2) with a typed constant like 4500. Standard SUM(D2:D6) blends live formulas and hardcoded plugs into a single total with zero warning.
Adding an audit column E2:E6 = ISFORMULA(D2) splits the grand total into two reconciled buckets: SUMIF(E2:E6, TRUE, D2:D6) sums strictly formula-calculated rows, while SUMIF(E2:E6, FALSE, D2:D6) isolates manual plugs. In Arte Sheets, teams co-editing financial models in real time can pair ISFORMULA audit checks with Suggest-only collaborator roles, @mention cell comments explaining approved plugs, and version history recovery.
| Line Item | Units * Rate | Line Total (D) | ISFORMULA(D)? | Audit Bucket |
|---|---|---|---|---|
| Core Licenses | 40 * 120 | =B2 * C2 (4,800) | TRUE | Formula Subtotal |
| Onboarding Fee | 1 * 2,500 | 3000 (Typed Override) | FALSE | Manual Override Plug |
| Support Retainer | 12 * 350 | =B4 * C4 (4,200) | TRUE | Formula Subtotal |
| Custom Migration | 1 * 1,800 | 1500 (Typed Override) | FALSE | Manual Override Plug |
| API Add-On | 25 * 80 | =B6 * C6 (2,000) | TRUE | Formula Subtotal |
Sum Formula-Calculated Cells Only
Sums only those line totals in D2:D6 whose ISFORMULA flag in E2:E6 is TRUE.
=SUMIF(E2:E6, TRUE, D2:D6)Sum Hardcoded Manual Overrides & Count Plugged Cells
Isolates the dollar value of hardcoded overrides so Formula_Total + Override_Total reconciles to SUM(D2:D6).
=SUMIF(E2:E6, FALSE, D2:D6)Try the example
Use the original values or change them to test your understanding.