What's the difference between SUMIFS and SUMPRODUCT for FP&A aggregations?

SUMIFS is faster and should be your default for straightforward multi-condition roll-ups of revenue, headcount costs, or expenses. SUMPRODUCT is necessary when you need weighted calculations - like blended gross margin across SKUs - or conditional logic that SUMIFS can't express in a single formula. On datasets over 5,000 rows, SUMIFS consistently outperforms SUMPRODUCT because it's optimized for columnar range operations.

Should I use INDEX/MATCH or XLOOKUP in a financial model?

Use XLOOKUP if the model lives entirely in Google Sheets or Excel 365 - the syntax is cleaner and the not-found default is built in. Use INDEX/MATCH if the file might be opened in Excel 2019 or earlier; XLOOKUP isn't available there and returns a #NAME? error. For models shared with bank syndicate teams or investors on older Excel versions, INDEX/MATCH is the safer default regardless of where you built it.

Why does XIRR return #NUM! in my LBO model?

XIRR uses Newton-Raphson iteration and fails to converge when the seed rate is too far from the actual IRR, or when the cash flow pattern has multiple sign changes. Change the third argument to a different starting estimate: 0.1 for normal deals, 0.3-0.5 for high-return scenarios. Also verify your date column contains actual date values - text strings formatted to look like dates won't work and produce #NUM! without an obvious error message.

How do I build a rolling 36-month period structure using EOMONTH?

Put your model start date in a single Assumptions cell (e.g., `Assumptions!$B$3`). In the period header row, use `=EOMONTH(Assumptions!$B$3, COLUMN(A1)-1)` and drag right across 36 columns. This generates 2026-01-31, 2026-02-28, 2026-03-31, etc. automatically, with correct February and 30-day month handling. When the start date changes, all 36 headers and every formula referencing them update simultaneously.

What's the right IFERROR default for rate assumptions versus balance items?

For balance sheet items and aggregation formulas - revenue, operating expenses, headcount costs - returning 0 from IFERROR is safe, since a missing item means zero contribution. For rate assumptions like WACC, gross margin %, or a growth rate, return a text string like "CHECK" instead. A zero rate looks like valid data and won't trigger a visible error downstream; "CHECK" propagates visibly through every cell that depends on it, surfacing the problem before the model gets shared.

FP&A Formula Reference: Google Sheets (2026)

Core FP&A Aggregation Formulas: SUMIFS and SUMPRODUCT

SUMIFS is the workhorse of P&L roll-ups. When your transaction data sits on a raw tab and your summary lives two tabs over, this is the formula doing the heavy lifting:

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

That pulls revenue for a specific account and date range from your transactions tab, with period endpoints anchored in Assumptions. Change one date in Assumptions and the entire P&L recalculates.

SUMPRODUCT earns its place when you need weighted averages or multi-condition aggregations that SUMIFS can't express cleanly. Blended gross margin across a product mix:

=SUMPRODUCT(('SKU Data'!C2:C500)*('SKU Data'!D2:D500))/SUMPRODUCT('SKU Data'!C2:C500)

Column C is revenue by SKU, column D is gross margin percentage per SKU. This weights margin by revenue size rather than treating each SKU equally - which matters when your top 3 SKUs represent 68% of volume.

SUMPRODUCT handles up to 100,000 rows without performance issues in Sheets, though beyond 50,000 rows you'll want to check whether a named range or helper column gets you the same result faster.

FP&A Lookup Formulas: INDEX/MATCH and XLOOKUP

VLOOKUP has one fatal flaw in financial models: it breaks when you insert a column. INDEX/MATCH doesn't:

=INDEX('Assumptions'!$C$2:$C$50,MATCH('Returns Analysis'!$A5,'Assumptions'!$B$2:$B$50,0))

This pulls a discount rate or growth assumption from your Assumptions tab by matching the row label. Inserting columns in either tab won't break it.

XLOOKUP, available in Google Sheets since 2023 per Google's function release notes, simplifies this considerably:

=XLOOKUP('Returns Analysis'!$A5,'Assumptions'!$B$2:$B$50,'Assumptions'!$C$2:$C$50,"CHECK",0)

The fourth argument returns a default when no match is found - critical in models where incomplete data shouldn't silently propagate as zeros. XLOOKUP also searches right-to-left, which occasionally matters when your lookup column isn't the leftmost one.

Which to use? If the model needs to run in Excel as well as Sheets, XLOOKUP is available in Excel 365 but not Excel 2019 or earlier. INDEX/MATCH works everywhere. For a bank syndicate DCF that'll be shared with lenders on older Excel, INDEX/MATCH is the safer call.

XIRR for LBO and Fund Returns

IRR assumes cash flows at equal intervals. XIRR doesn't, which makes it the right formula for LBO returns analysis, VC fund modeling, or any situation where capital calls and distributions don't follow a neat monthly schedule.

=XIRR('Cash Flows'!C2:C36,'Cash Flows'!B2:B36,0.15)

Column B holds dates in YYYY-MM-DD format, column C holds cash flows (negative for outflows, positive for inflows). The third argument seeds the iteration at 15%. Per Google's Sheets function documentation, XIRR uses Newton-Raphson iteration with a tolerance of 0.000001% and a maximum of 100 iterations - which means unusual cash flow patterns with multiple sign changes can cause it to fail.

If XIRR returns #NUM!, try a different seed rate. For a standard 5-year LBO with a 2.5x MOIC targeting 18-25% returns, seeding at 0.2 works reliably. For distressed situations or deals with unusual distribution timing, test seeds between 0.05 and 0.50.

FP&A Date Formulas: EDATE and EOMONTH

Rolling forecast periods require date arithmetic that stays correct when months have different lengths. EDATE and EOMONTH handle this.

EDATE moves a date forward or backward by N months, landing on the same day of the month:

=EDATE(Assumptions!$B$3,Assumptions!$C5)

Where $B$3 is your model start date and $C5 is the period offset. A 36-month projection: change the start date and every period header recalculates.

EOMONTH is more useful when anchoring to period-end dates, which most financial models do:

=EOMONTH(Assumptions!$B$3,Assumptions!$C5-1)

The -1 offset means "end of the Nth month after the start date." For a model starting 2026-01-31, this returns 2026-02-28, 2026-03-31, 2026-04-30 - correct month-ends, no manual adjustment for February or 30-day months.

Combine with SUMIFS for rolling period pulls:

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&EOMONTH(Assumptions!$B$3,A5-2)+1,'P&L'!B:B,"<="&EOMONTH(Assumptions!$B$3,A5-1))

This defines clean month boundaries using previous month-end +1 as the period open, and current month-end as the close. No hardcoded dates anywhere.

Error Handling in Multi-Tab Models

A model with 8+ linked tabs will surface errors. IFERROR and IFNA keep them from cascading.

=IFERROR(INDEX('Assumptions'!$C:$C,MATCH(A5,'Assumptions'!$B:$B,0)),0)

Zero is the right default for aggregation formulas where a missing item means zero contribution. For rate assumptions - WACC, growth rate, gross margin - returning 0 silently is dangerous. Use a visible string instead:

=IFERROR(XLOOKUP(A5,'Assumptions'!$B:$B,'Assumptions'!$C:$C),"CHECK")

"CHECK" surfaces in every downstream cell that depends on this rate, catching the problem before the board pack goes out rather than after.

IFNA is the tighter version: it catches only #N/A errors, not #VALUE! or #REF!. Use IFNA when you want other error types to stay visible rather than get swallowed by a blanket handler.

ARRAYFORMULA for Column-Wide Calculations

ARRAYFORMULA extends a formula across a range without copy-pasting it row by row. For contribution margin analysis by SKU across 5,000 rows:

=ARRAYFORMULA(IF('P&L'!B2:B5001="","",('P&L'!C2:C5001-'P&L'!D2:D5001)/'P&L'!C2:C5001))

The IF wrapper skips blank rows, which prevents #DIV/0! errors from propagating into your summary totals. New SKUs added to the data tab get picked up automatically without touching the formula.

One trade-off: ARRAYFORMULA in a column blocks manual entry in cells below it. If colleagues need to hard-code overrides for specific rows, a helper column approach is cleaner.

Formula Reference Table

Use Case	Formula	Why It Wins
Multi-condition revenue roll-up	SUMIFS	Multiple criteria, fast on large datasets
Weighted average margin by SKU	SUMPRODUCT	Weights by volume, handles complex conditions
Cross-tab assumption lookup	INDEX/MATCH	Survives column insertions, works in all Excel versions
Lookup with a safe default	XLOOKUP	Cleaner syntax, native not-found handling
Returns on uneven cash flows	XIRR	Handles actual dates, not just equal intervals
Period header generation	EDATE / EOMONTH	Correct month-end arithmetic for any month length
Selective error suppression	IFERROR / IFNA	Different scopes - use IFNA when partial errors should stay visible
Column-wide calculated fields	ARRAYFORMULA	Extends automatically to new rows

Where ModelMonkey Fits

Getting these formulas right in context - which tab is the source, which column holds the date, whether a particular lookup needs IFNA or IFERROR - is where the time actually goes. ModelMonkey reads your sheet structure and generates formulas that reference your actual tab and column names, not generic placeholders. It handles the multi-tab reference syntax that generic AI tools tend to get wrong, and it runs in both Google Sheets and Excel.

If you're wiring up a fresh set of cross-tab lookups or inheriting a model where the formula logic isn't documented anywhere, that's where it saves the most time.

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