What is the difference between SUMIFS and SUMIF in Google Sheets formulas?

SUMIF takes a single criteria range and a single criterion. SUMIFS takes multiple criteria ranges and criteria, evaluated with AND logic. In practice, always use SUMIFS - it's a strict superset of SUMIF and the syntax is more consistent. In financial models, you'll almost always need at least two criteria (date range + category), which requires SUMIFS. Example: `=SUMIFS('P&L'!D:D, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!C:C, "SaaS")` sums revenue on the P&L tab where the date is on or after the start date and the segment is SaaS.

How do ARRAYFORMULA and regular drag-fill formulas differ in Google Sheets?

ARRAYFORMULA expands a single formula across an entire range from one cell, without copying the formula to each row. This means new rows added below automatically get the formula applied - no manual extension needed. The tradeoff: ARRAYFORMULA doesn't work with every function (INDEX/MATCH inside ARRAYFORMULA won't work; XLOOKUP will in most cases as of 2026), and a single error in the expansion range can break the entire array. For high-row-count models where data grows dynamically, ARRAYFORMULA is more reliable than drag-fill because drag-fill silently stops at whatever row you last copied to.

Why does XLOOKUP return a blank instead of zero in Google Sheets, and how do I fix it?

When no match is found, XLOOKUP's default `if_not_found` behavior returns an empty string `""` - not `0` and not `#N/A`. This matters because downstream multiplication treats `""` as zero (correct), but downstream AVERAGEIF or COUNTIF formulas may treat it differently than a true zero, and IF checks like `=IF(A1="", ...)` will flag the cell as empty. Fix: explicitly set the `if_not_found` argument to `0` for any numeric lookup in a financial model: `=XLOOKUP(A2, 'Rates'!A:A, 'Rates'!B:B, 0)`. This makes the zero explicit, consistent, and auditable.

What is Google Sheets' cell limit, and how does it affect large financial models?

Google Sheets caps at 10 million cells per workbook, across all tabs combined. A single tab with 1,000 columns and 10,000 rows already hits 10 million. In practice, consolidation models using IMPORTRANGE to pull multiple entity P&Ls are most at risk - 50 entities with a 200-column monthly model each adds up to 10 million cells before you add summary tabs. When you hit the limit, new cells stop calculating silently. The fix is to reduce full-column references (D:D style) to scoped ranges (D2:D2000), and to use a dedicated Inputs tab that imports only the rows you actually use.

Google Sheets Formulas for FP&A Models (2026)

Q: How do I make IMPORTRANGE data refresh reliably in Google Sheets?

IMPORTRANGE refreshes on its own schedule, roughly every 30 minutes, not on every recalculation. You can force a refresh by editing the formula (adding a space and removing it), but that's not practical during a live board meeting. The reliable pattern: use IMPORTRANGE to pull source data into a dedicated "Inputs" or "Raw" tab, then reference that tab with normal cross-tab formulas everywhere else in the model. This isolates the refresh timing to one place and keeps your calculation layer clean. Note that the first time you reference a new source file, IMPORTRANGE shows a `#REF!` error until the file owner explicitly authorizes the connection - this is a one-time permissions step, not a formula error.

This article covers the formulas that actually appear in multi-tab FP&A models, where each one diverges from Excel, and how to handle the gaps.

Cross-Tab Google Sheets Formulas: SUMIFS Across Tabs

The most common formula pattern in any FP&A model: summing a column on one tab based on criteria from another. In Google Sheets, this works exactly as you'd expect - until you add a dynamic criteria range that spans tabs.

=SUMIFS('P&L'!D:D, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!B:B, "<=" & Assumptions!$B$4, 'P&L'!C:C, Returns!$A7)

That formula pulls contribution-margin-by-SKU data from the P&L tab, filtered by the date window in Assumptions and the segment key in Returns. It works correctly. What doesn't work: using a named range from another tab inside SUMIFS criteria when that named range is defined as a multi-cell range. Google Sheets throws #VALUE! - Excel handles it silently.

Workaround: define named ranges as single-cell anchors on the Assumptions tab, then reference the range explicitly in the formula. Named ranges in Google Sheets are capped at 1,000 per workbook, and names max out at 250 characters - both limits you'll hit before Excel.

The bigger gotcha with cross-tab SUMIFS is volatile recalculation. Columns that span all rows (D:D style) force a full-column scan on every change. On a model with 12 monthly tabs and 40+ SKUs, this adds up. Scoped ranges like D2:D5000 recalculate measurably faster - in practice, switching from full-column to scoped references on a complex board-pack model can cut recalculation time by 30-50% (though the exact delta depends heavily on cell count and formula depth).

ARRAYFORMULA: The Google Formula That Replaces Entire Helper Columns

In Excel, if you want to apply a formula down an entire column you either drag it, copy-paste it, or use a table with structured references. In Google Sheets, ARRAYFORMULA lets you do it from a single cell.

=ARRAYFORMULA(IF('P&L'!B2:B5000 >= Assumptions!$B$3, 'P&L'!D2:D5000 * (1 - Assumptions!$E$7), 0))

That's a gross-margin-adjusted contribution calculation applied to every row in the P&L, inline, from one cell. No helper column, no drag-fill, no formula that silently didn't copy to the last 3 rows of new data.

Where it breaks: ARRAYFORMULA doesn't work with every function. VLOOKUP inside ARRAYFORMULA works; INDEX/MATCH inside ARRAYFORMULA does not (the MATCH part evaluates but returns only the first result). XLOOKUP inside ARRAYFORMULA works in most cases as of 2026, but test it when your lookup array is on a different tab - behavior has been inconsistent across Sheets versions.

The other failure mode: ARRAYFORMULA expands down into any row that has data, including rows you consider "blank" but have a trailing space or invisible character. One import from an ERP that added a newline to the last row broke a 5,000-row ARRAYFORMULA that had been stable for months. (The whitespace cleanup patterns are worth internalizing before this bites you.)

XLOOKUP vs. INDEX/MATCH: Which Google Sheets Formula to Use in a Model

Google Sheets added XLOOKUP in 2023. Whether you should migrate your models to it depends on what you're building.

XLOOKUP advantages over INDEX/MATCH in Sheets:

Cleaner syntax when looking up across tabs: =XLOOKUP(Returns!A7, 'P&L'!C:C, 'P&L'!D:D, 0) vs. the INDEX/MATCH equivalent
Built-in if_not_found argument means no wrapping IFERROR
Returns the actual match value rather than position, so it chains better in nested formulas

The catch: XLOOKUP returns an empty string "" by default when there's no match and you've set if_not_found to "". That empty string propagates differently than a zero or an NA(). A downstream SUMIFS or multiplication will treat it as zero - fine. A downstream AVERAGEIF will exclude it - also fine. A downstream formula that checks =IF(A1="", ...) will flag it incorrectly if the cell was intentionally zero. Set if_not_found to 0 for numeric models, not "".

INDEX/MATCH still wins for:

Array-returning lookups (returning an entire column range)
Two-way lookups where both row and column are dynamic
Models that need to run in Excel too - XLOOKUP in Sheets has a slightly different syntax than Excel's XLOOKUP for some edge cases

For a bank syndicate DCF where the model will be shared with parties running Excel, stick with INDEX/MATCH. For an internal board pack that lives in Sheets permanently, XLOOKUP is cleaner.

Date Formulas: The Google Sheets Functions That Actually Appear in Models

Date handling is where Sheets and Excel diverge most, and where the bugs are hardest to find because the output looks correct until it doesn't.

EOMONTH is the workhorse for period-end calculations. =EOMONTH(Assumptions!$B$3, 0) returns the last day of the month. =EOMONTH(Assumptions!$B$3, -1)+1 returns the first day. This pattern shows up in every rolling 12-month P&L or quarterly cash flow model.

EDATE advances a date by months. =EDATE(C3, 12) gives you exactly one year forward - no 365-day approximation, no leap year error. Use this for debt amortization schedules and covenant testing dates.

The date serial number divergence matters if you're importing from Excel: Excel starts its date serial at January 1, 1900; Google Sheets does too, but Excel incorrectly includes February 29, 1900 as a valid date (it wasn't). Any date before March 1, 1900 is offset by one day between the two systems. Not relevant for a DCF, very relevant if you're reconciling historical data imported from a legacy system.

WORKDAY and NETWORKDAYS use your locale's holiday calendar by default if you pass a region code - useful for DSO calculations where payment timing matters. Most analysts skip the holiday argument. Don't, if you're modeling cash receipts for a business with meaningful holiday exposure.

IMPORTRANGE: Powerful Google Sheets Formula, Fragile in Practice

IMPORTRANGE pulls a range from another Google Sheet entirely. For a multi-entity model where each entity has its own file, it's the standard consolidation mechanism.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/[ID]", "P&L!D2:D62")

The authorization step is manual and per-file. The first time you reference a new source file, you get a #REF! error until the user explicitly grants access. That's not a formula error - it's a permissions gate. Brief your team before they panic during a close.

The more serious issue: IMPORTRANGE is not instant. Large ranges (1,000+ rows, multiple columns) can take 3-5 seconds to load on open. They also don't refresh on recalculate - they refresh on their own schedule, roughly every 30 minutes, or when you force it by editing the formula. For a live model during a board meeting where numbers are changing, this is a real problem. The fix is to use IMPORTRANGE as a data-pull layer into a dedicated "Inputs" tab, then reference that tab with normal cross-tab formulas everywhere else. One refresh point, clean downstream.

Google Sheets caps at 10 million cells per workbook. A consolidation model pulling 50 entities via IMPORTRANGE, each with a 200-column monthly P&L, can approach that ceiling faster than expected. When you hit it, new cells simply won't calculate.

Formula Error Reference

Error	Common Cause in FP&A Models	Fix
`#REF!`	IMPORTRANGE not yet authorized, or source range deleted	Re-authorize or repair source range
`#VALUE!`	Named range passed as SUMIFS criteria, or text in numeric range	Use single-cell anchors; audit source data types
`#N/A`	XLOOKUP or MATCH can't find key - often a trailing space or format mismatch	TRIM source keys; use VALUE() wrapper if lookup key is a number stored as text
`#DIV/0!`	Period with zero revenue in margin calc	Wrap with IFERROR or add an IF guard on the denominator
`#ERROR!`	Syntax error unique to Sheets (not Excel)	Check for mismatched quotes or unsupported nested ARRAYFORMULA

Where ModelMonkey Fits

The formulas above are not hard to write. What's hard is auditing them across 8 tabs, spotting the XLOOKUP that returns "" instead of 0, or finding the ARRAYFORMULA that stopped expanding because a blank row appeared at row 3,847.

ModelMonkey sits inside Google Sheets as an AI assistant and can scan your active model for formula errors, broken references, and range inconsistencies - the kind of audit that takes an analyst an hour and still misses things. It reads the actual formula layer, not just the displayed values, so it catches the silent failures before they surface in a board meeting.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.