The underlying mechanic: Google Sheets separates data from display. Filters hide rows - they don't remove them. Sorts reorder what you see - they don't rewire formula references. Every formula that breaks under these conditions was built on an assumption that no longer holds.
The Three Failure Modes
1. Aggregation That Ignores Filter State
The most common complaint in FP&A: you filter a 180-row P&L to show only COGS components, and the column total still shows $4.2M instead of the $1.6M you're staring at.
=SUM('P&L'!C2:C200) doesn't care what's visible. Neither do AVERAGE, MAX, COUNTIF, or SUMIFS. They operate on the full dataset, always.
The fix is SUBTOTAL, which has 11 parallel aggregate functions that respect filter state:
=SUBTOTAL(9,'P&L'!C2:C200) // SUM of visible rows only
=SUBTOTAL(2,'P&L'!C2:C200) // COUNT of visible rows only
=SUBTOTAL(1,'P&L'!C2:C200) // AVERAGE of visible rows only
The function codes: 9=SUM, 2=COUNT, 1=AVERAGE, 3=COUNTA, 4=MAX, 5=MIN. If you need to also ignore manually hidden rows (not just filtered), use the 100-series: SUBTOTAL(109,...). The Google Sheets documentation for SUBTOTAL describes this as: "If there are other SUBTOTALS within ref1, ref2, etc., these nested SUBTOTALS are ignored to avoid double counting."
That nested-SUBTOTAL behavior matters in hierarchical P&Ls. A gross profit subtotal and a revenue grand total can both use SUBTOTAL on overlapping ranges - the grand total won't double-count the subtotals. SUM would.
AGGREGATE goes further. It ignores errors and hidden rows simultaneously, which SUBTOTAL can't do:
=AGGREGATE(9,5,'P&L'!C2:C200) // SUM, ignore hidden rows AND errors
=AGGREGATE(9,7,'P&L'!C2:C200) // SUM, ignore hidden rows AND errors
The second argument is the option code: 5 = ignore hidden rows, 6 = ignore errors, 7 = ignore both. When your contribution margin model has #N/A values in filtered rows from unmatched SKUs, AGGREGATE is the only native function that won't blow up the total.
2. Position-Dependent References That Break After Sorting
This failure is nastier because it doesn't error - it silently pulls wrong numbers.
It surfaces in headcount models, deal pipelines, and SKU tables where someone used ROW() or positional OFFSET() to build a lookup:
// Looks fine; breaks when sorted
=OFFSET(Assumptions!$B$1, ROW()-1, 0)
ROW() returns the absolute row number in the sheet. Sort the HC tab by department and ROW()-1 now maps to a completely different assumption. On a 12-tab LBO model before a bank syndicate review, this is a credibility problem.
The rule: never use ROW() or positional OFFSET() to look up values across tabs unless you can guarantee both tabs will always share identical sort order. That guarantee evaporates the moment a second person touches the file.
Fix: replace positional lookups with key-based lookups.
// Breaks when sorted:
=OFFSET(Assumptions!$B$1, ROW()-1, 0)
// Survives any sort order:
=INDEX(Assumptions!$B:$B, MATCH(HC!A2, Assumptions!$A:$A, 0))
For cross-tab references in a Returns Analysis pulling from an Assumptions tab - if Assumptions is ever sorted by row type, any direct row reference like =Assumptions!C15 is now pointing at the wrong metric. Use =INDEX(Assumptions!$C:$C, MATCH("Terminal Value", Assumptions!$B:$B, 0)) instead. It finds the right row regardless of what order the data is in.
3. SUMIFS in a Filtered Range: Working as Designed
This one looks broken but isn't.
=SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!D:D, "Revenue")
If you filter 'P&L' to show only Q1 rows, this formula still returns the full Revenue total matching those date criteria - regardless of filter state. SUMIFS applies its own criteria to the entire column. The visual filter is invisible to it.
This is by design. SUMIFS criteria and sheet filters are parallel mechanisms. If you want "sum rows the user currently has visible," SUMIFS can't do that. SUBTOTAL can, but only if all visible rows should be included with no further criteria.
If you need both - filter-respecting aggregation with criteria - as of June 2026 the cleanest approach is FILTER() inside SUM():
=SUM(FILTER('P&L'!C:C, ('P&L'!B:B >= Assumptions!$B$3) * ('P&L'!D:D = "Revenue")))
This applies criteria the same way SUMIFS does but uses the FILTER function's evaluation engine. It still doesn't read the active visual filter state - but in most modeling contexts that's correct. You want criteria-based aggregation, not screen-state-dependent aggregation.
Running Totals in Sorted Tables
The anchor pattern =SUM($C$2:C2) works correctly after a sort. Each row's formula expands from row 2 to its current row, which is what you want.
What breaks is pairing a running total with ROW()-based logic:
// Wrong after sort - OFFSET looks one row up in the sheet, not one row up in sequence
=IF(ROW()=2, C2, C2 + OFFSET(D2, -1, 0))
After sorting, OFFSET(-1,0) still points one row up in the sheet - which now holds a different record. Replace with the anchor pattern:
=SUM($C$2:C2)
This always sums from row 2 to the current row and recalculates correctly under any sort order.
Cross-Tab References and the Silent Reorder Problem
The version of this problem that gets missed: everything tied out Monday, someone sorted the data tab Tuesday to prepare for a presentation, and now your cash flow tab is pulling revenue from the wrong rows. No error. Just wrong numbers.
Every direct cell reference to a tab that can be sorted is permanently fragile. ='P&L'!C15 becomes wrong the moment anyone reorders that tab. =INDEX('P&L'!C:C, MATCH("Operating Income", 'P&L'!B:B, 0)) is stable regardless of sort order.
In a three-statement model with FCFF and Returns tabs, this is the minimum viable standard for any reference that crosses tab boundaries.