Does ARRAYFORMULA work with SUMIFS?

SUMIFS handles ranges natively, so ARRAYFORMULA doesn't change its basic behavior. The specific use case where ARRAYFORMULA adds value is when you want SUMIFS to return a different result for each row in a varying-criteria column - for example, `=ARRAYFORMULA(SUMIFS('P&L'!C:C, 'P&L'!A:A, ">=" & Scenarios!B2:B20))` returns 20 separate SUMIFS totals, one per date in B2:B20. Outside that pattern, wrapping SUMIFS in ARRAYFORMULA usually does nothing. Test this before committing; behavior can vary depending on how criteria ranges are structured.

Does ARRAYFORMULA slow down Google Sheets?

It depends on what's inside it. An ARRAYFORMULA running arithmetic or VLOOKUP on a static range is typically faster than thousands of individual cell formulas, because Sheets evaluates the array in a single dependency pass rather than resolving each cell separately. The problem case is ARRAYFORMULA containing volatile functions (TODAY, NOW, RAND) or referencing tabs that recalculate constantly - the entire array recalculates on every edit. For models with 50,000+ rows or heavy cross-tab dependencies, benchmark recalculation time before deploying.

Can ARRAYFORMULA reference other tabs in a multi-tab model?

Yes, and cross-tab references are one of its strongest use cases. A formula like `=ARRAYFORMULA(IF('P&L'!B2:B="Revenue", 'P&L'!C2:C * Assumptions!$B$3, 0))` lives on one tab, reads from a second, and applies a rate from a third. The formula automatically covers new rows added to the P&L tab without any maintenance. This pattern is standard in multi-tab models where raw data lives on one tab and calculated outputs feed summary tabs downstream.

What's the difference between ARRAYFORMULA and SUMPRODUCT?

They serve different purposes. SUMPRODUCT aggregates arrays into a single value - it multiplies arrays element-by-element and sums the result, which is useful for weighted averages, multi-criteria counts, or conditional sums where you want one number out. ARRAYFORMULA expands a formula to produce one output per input row, which is useful for calculated columns. Use SUMPRODUCT when you want a single aggregated result; use ARRAYFORMULA when you want a column of results. They're complementary - a common pattern is ARRAYFORMULA producing a tag column that SUMPRODUCT then aggregates.

Why does ARRAYFORMULA show the "would overwrite data" error?

This error fires when ARRAYFORMULA tries to write its output into cells that already contain values. Sheets won't overwrite existing cell content with array propagation. The fix is to clear every cell below your ARRAYFORMULA formula in that column before entering the formula. If the column was previously populated with dragged-down formulas, select the cells below row 2 (or wherever your ARRAYFORMULA lives), delete them, then re-enter the ARRAYFORMULA. The error also appears if a second ARRAYFORMULA in the same column tries to output into the range already claimed by the first.

What Is ARRAYFORMULA Used For in Google Sheets?

For FP&A work, that distinction matters more than it sounds. A dragged-down formula is fragile by design. One inserted row above your data range breaks the fill; one deleted row leaves a gap your SUMIFS will skip. ARRAYFORMULA owns the whole column unconditionally, which is why it shows up in every model that gets edited by more than one person.

Where ARRAYFORMULA Actually Earns Its Keep

Calculated Columns on Raw Transaction Data

The most common use: computing a derived metric for every row in a data tab without touching each cell individually.

Say your Transactions tab has 8,000 rows of revenue by customer. You need net revenue after a 2.9% processing fee and a $0.30 per-transaction cost, both stored in your Assumptions tab:

=ARRAYFORMULA(
  IF('Transactions'!B2:B <> "",
     'Transactions'!C2:C * (1 - Assumptions!$B$5) - Assumptions!$B$6,
     "")
)

One formula in NetRevenue!D2 covers all 8,000 rows. The IF condition gates on non-empty rows so the formula doesn't output zeros below your data. Change the processing rate in Assumptions!B5 and every row recalculates instantly. This replaces the pattern of copying a formula 8,000 rows down, which generates 8,000 separate cell evaluations against a single dependency. ARRAYFORMULA evaluates in a single pass, which is typically 30-60% faster for large ranges.

Dynamic Lookups Without Maintenance

VLOOKUP (or INDEX/MATCH) inside ARRAYFORMULA fires a lookup for every row in the input range and returns the results as a column. Wrap with IFERROR to handle unmapped rows cleanly:

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP('Orders'!B2:B, 'SKU Master'!A:C, 3, 0),
    "Unmapped"
  )
)

This wires your entire order history to your SKU Master in one formula. Add a new SKU to the master and every matching order picks it up on the next recalculation. For contribution margin models by SKU, this pattern eliminates an entire class of maintenance work: the lookup column stays current without anyone touching it.

For cases where you need to match on two criteria - say, role and level from a rate card - concatenated INDEX/MATCH handles it:

=ARRAYFORMULA(
  IFERROR(
    INDEX('Rate Card'!C:C,
      MATCH('Headcount'!B2:B50 & 'Headcount'!C2:C50,
            'Rate Card'!A:A & 'Rate Card'!B:B, 0)
    ),
    0
  )
)

This fires 50 INDEX/MATCH lookups simultaneously, pulling loaded salary costs for each headcount row by role-and-level combination.

Conditional Row Tagging That Feeds Downstream Aggregations

One pattern that appears constantly in board pack preparation: you need to tag each transaction row based on multiple criteria, then aggregate the tagged rows on a summary tab. ARRAYFORMULA handles the tag column; SUMIFS consumes it cleanly.

=ARRAYFORMULA(
  IF(
    ('P&L'!A2:A >= Assumptions!$B$3) *
    ('P&L'!A2:A <= Assumptions!$B$4) *
    ('P&L'!D2:D = "SaaS"),
    "Include",
    "Exclude"
  )
)

The * operator acts as AND across boolean arrays. The resulting column on HelperCol feeds a =SUMIFS('P&L'!C:C, HelperCol!E:E, "Include") on your summary tab. This is far easier to audit than cramming nested criteria into a single SUMIFS - and it gives you a visible, inspectable column that shows exactly which rows are being counted.

The summary formula that consumes it:

=SUMIFS('P&L'!C:C, 'P&L'!D:D, "SaaS", HelperCol!E:E, "Include")

Cross-Tab Aggregations With Dynamic Criteria

When your criteria come from a dynamic list - say, a list of 15 cost centers that changes quarterly - ARRAYFORMULA lets SUMIFS return one total per criteria row rather than one total for all of them:

=ARRAYFORMULA(
  SUMIFS('P&L'!C:C,
         'P&L'!B:B, Assumptions!A2:A20,
         'P&L'!A:A, ">=" & Assumptions!$B$3)
)

This drops 20 cost-center totals into 20 cells with one formula, all filtered by the period start date in Assumptions!B3. Change the period or the cost center list and the whole output updates. For a quarterly board pack with 8-12 tabs of this structure, it cuts the formula count significantly without sacrificing auditability.

What ARRAYFORMULA Doesn't Handle Well

SUMIFS, COUNTIFS, and AVERAGEIFS already handle arrays natively - you don't need ARRAYFORMULA to make them work across ranges. Where ARRAYFORMULA does add value with those functions is specifically when you want them to return a different result per row based on varying criteria, which is a specific use case worth testing before committing to it.

Performance is the other constraint. An ARRAYFORMULA referencing a volatile function like TODAY() or NOW() recalculates the entire array on every spreadsheet edit. For a model with 50,000+ rows pulling from a volatile reference, this can make the sheet noticeably slow. According to Google's official Sheets documentation (as of July 2026), "Functions that recalculate every time the spreadsheet changes... can significantly impact performance when used in large data ranges." Test recalculation time before deploying ARRAYFORMULA in high-traffic models.

Auditability is worth considering too. When a bank syndicator or external auditor reviews your model, they expect formula logic in every cell. A column where 4,999 of 5,000 cells are blank (populated by propagation from row 2) can look broken to someone unfamiliar with ARRAYFORMULA. For models that go to external parties, dragged-down formulas are sometimes the better choice - not because they're more correct, but because they're more legible to the reviewer.

The most common error you'll hit: "Array result was not expanded because it would overwrite data in [cell]." This fires when ARRAYFORMULA tries to write into cells that already have content below. Fix: clear the cells below the formula before entering it.

The Production Pattern: ARRAYFORMULA + IFERROR

The combination =ARRAYFORMULA(IFERROR(..., "")) is how ARRAYFORMULA actually lives in production models. IFERROR catches lookup misses, division-by-zero from empty rows, and type mismatches that would otherwise fill your column with #N/A or #VALUE!. Google Sheets caps at 10 million cells per spreadsheet - a column of uncaught errors in a model with multiple large tabs eats into that limit faster than expected, and cascading #N/A values break every SUMIFS downstream.

=ARRAYFORMULA(
  IFERROR(
    'P&L'!C2:C / 'P&L'!B2:B,
    ""
  )
)

The empty string keeps cells visually clean and prevents errors from propagating into aggregations on other tabs. It's a two-second addition that saves you from debugging #VALUE! on your Returns Analysis tab at 11pm before a board meeting.

If you're working in Excel rather than Sheets, the behavior differs significantly - Excel doesn't have ARRAYFORMULA as a function; dynamic arrays and Ctrl+Shift+Enter legacy arrays serve different roles. The Excel ARRAYFORMULA guide covers the three equivalents that actually work.

ModelMonkey's AI assistant in Google Sheets is useful for exactly the kind of ARRAYFORMULA debugging that isn't about understanding the syntax - it's about tracing why an existing array formula stopped propagating after a structural edit, or identifying which cell is causing the "would overwrite data" conflict. It reads the formula in context and identifies the issue without you needing to reverse-engineer the dependency chain.

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