Does ARRAYFORMULA work across tabs in Google Sheets?

Yes. Reference other sheets normally inside the formula: `=ARRAYFORMULA(VLOOKUP('Transactions'!A2:A, 'Chart of Accounts'!$A:$C, 3, 0))`. The open-ended column reference (`A2:A`) covers all data rows in the source tab automatically. As of June 2026, cross-tab `ARRAYFORMULA` is stable in Google Sheets, though models pulling from 4+ source tabs simultaneously can slow recalculation noticeably on larger datasets.

Why is my ARRAYFORMULA filling blank rows with zeros or errors?

You're missing the empty-row guard. Wrap your formula in an `IF` check on the key column: `=ARRAYFORMULA(IF(A2:A="","", [your formula]))`. Without it, `ARRAYFORMULA` evaluates every row in the column, including empty ones, and returns whatever the formula produces on a blank input - usually 0, `#N/A`, or an empty string that still occupies the cell. The `IF` gate short-circuits evaluation for blank rows and keeps your aggregation ranges clean.

Can I use ARRAYFORMULA with SUMIFS or COUNTIFS?

Not in the way most people try. `SUMIFS` and `COUNTIFS` aggregate a range into a scalar; wrapping them in `ARRAYFORMULA` doesn't make them evaluate row-by-row. For row-level conditional sums inside an array context, use `SUMPRODUCT` or `MMULT`. For most FP&A models, the right pattern is `ARRAYFORMULA` for classification columns (tagging, mapping, transforming) and `SUMIFS` outside it for aggregation - keep the two layers separate.

How do I output a true blank (not an empty string) from ARRAYFORMULA?

Use `IF(A2:A="", ,` with nothing after the comma: `=ARRAYFORMULA(IF(A2:A="", , [your formula]))`. A true blank won't be counted by `COUNT`, won't appear as a data point in a chart, and won't cause `COUNTA` to overcount. An empty string `""` looks identical in the cell but behaves like a value in every function that distinguishes between blank and non-blank - which causes quiet errors in `COUNTA`-based row counts and chart data ranges.

Does ARRAYFORMULA work in Excel?

No. Excel has its own dynamic array engine, introduced for Microsoft 365 in 2019, that achieves the same result without the wrapper function. In Excel, `=A2:A10 * B2:B10` entered in a single cell spills automatically. There's no `ARRAYFORMULA()` syntax. Formulas copied from Google Sheets need to be rewritten for Excel, not just pasted - the column reference style (`A2:A`) and the wrapper both need to be stripped out.

ARRAYFORMULA in Google Sheets: FP&A Analyst Guide

Google's official documentation describes it as enabling "the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays." Technically accurate. Completely fails to explain why it matters.

Here's what actually matters: one ARRAYFORMULA cell replaces thousands of individual formula cells in Sheets' dependency graph. That alone cuts recalculation time on large models significantly. More on the numbers below.

Why Dragging Formulas Down Breaks Financial Models

The drag-to-copy approach has one fatal flaw in a collaborative model: it's static. You drag to row 2,000, someone imports 2,400 rows of GL data next month, and rows 2,001-2,400 have no formula. Your SUMIFS on the classification column silently excludes $847K of transactions. The board pack reconciles, the detail doesn't. You find the error at 11pm.

ARRAYFORMULA covers the full column from your header row to the bottom of whatever data exists, automatically. New rows get the formula. Deleted rows don't leave orphaned cells. The formula lives in one place, which means it gets audited in one place.

How ARRAYFORMULA Works

Wrap your existing formula in =ARRAYFORMULA() and replace single-cell references with open-ended column references.

Single-cell version:

=IFERROR(VLOOKUP(A2,'GL Mapping'!$A:$B,2,0),"Unmapped")

ARRAYFORMULA version (put this in B2 only, leave B3 and below empty):

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,'GL Mapping'!$A:$B,2,0),"Unmapped"))

The A2:A notation means "A2 through the last row of data." Sheets evaluates this as a vector operation, applying the lookup across every row simultaneously rather than sequentially.

For multi-tab models, the same logic extends across sheets without any change in structure:

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP('Transactions'!A2:A, 'Chart of Accounts'!$A:$C, 3, 0),
    "Unclassified"
  )
)

And for a segment lookup that needs to match on two keys (account + department):

=ARRAYFORMULA(
  IF('Transactions'!A2:A="","",
    IFERROR(
      INDEX('Segments'!$C:$C,
        MATCH(
          'Transactions'!B2:B & "|" & 'Transactions'!D2:D,
          'Segments'!$A:$A & "|" & 'Segments'!$B:$B,
          0
        )
      ),
      "No Match"
    )
  )
)

That INDEX/MATCH with concatenated keys runs across every row in one formula. The equivalent drag-down approach would be 10,000+ individual formula cells in a typical quarterly model.

The Functions ARRAYFORMULA Supports (and Doesn't)

ARRAYFORMULA works with most standard functions: IF, IFERROR, VLOOKUP, INDEX/MATCH, LEFT, RIGHT, MID, TEXT, VALUE, arithmetic operators, and comparison operators.

It does not work natively with:

SUMIF / SUMIFS (these already return a scalar)
COUNTIF / COUNTIFS (same reason)
QUERY (already operates on ranges)
Statistical functions like AVERAGE or STDEV when you want a single aggregate result

The pattern that works in practice: ARRAYFORMULA handles the classification and transformation layer (tagging rows, computing derived values, mapping to categories), and SUMIFS handles aggregation separately. Don't try to make one formula do both.

Multi-Tab Examples for Real FP&A Models

Fiscal period tagging on a GL export:

=ARRAYFORMULA(
  IF('GL Export'!A2:A="","",
    VLOOKUP(
      EOMONTH('GL Export'!C2:C,0),
      'Fiscal Calendar'!$A:$B,
      2, 1
    )
  )
)

This tags every transaction with its fiscal period by matching the month-end date to your fiscal calendar tab. When the GL export grows from 8,400 to 11,200 rows after month-end close, the formula covers new rows automatically.

Contribution margin flag for SKU-level P&L (38.5% gross margin target, $4.2M revenue base):

=ARRAYFORMULA(
  IF('Sales Detail'!A2:A="","",
    IF(
      ('Sales Detail'!D2:D - 'Sales Detail'!E2:E) / 'Sales Detail'!D2:D
        < Assumptions!$B$7,
      "Below Threshold",
      "OK"
    )
  )
)

Assumptions!$B$7 holds your minimum acceptable contribution margin. Change it once and every row updates. No range to re-drag when you add 300 new SKUs.

Actuals-to-budget mapping with a two-dimension key:

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      'Actuals'!B2:B & "|" & 'Actuals'!C2:C,
      {'Budget'!$A:$A & "|" & 'Budget'!$B:$B, 'Budget'!$C:$C},
      2, 0
    ),
    0
  )
)

The concatenated pipe-delimited key handles the account-plus-department lookup that a plain VLOOKUP can't resolve on two columns.

The Empty Row Problem

The most common ARRAYFORMULA failure: the formula extends into blank rows and fills them with zeros, empty strings, or #N/A errors - which then pollute your SUMIFS ranges downstream.

The fix is an IF gate at the start of every ARRAYFORMULA:

=ARRAYFORMULA(
  IF(A2:A="","",
    [your actual formula here]
  )
)

When column A is empty, the formula returns an empty string rather than computing. This keeps aggregations clean.

For numeric outputs where you want a true blank rather than a zero, use IF(A2:A="", , (nothing after the comma) instead of "". A true blank doesn't register in COUNT, doesn't affect chart series, and won't cause a COUNTA to misfire. An empty string looks blank but behaves like a value - subtle and maddening.

Performance: One Formula vs. 10,000

Google Sheets caps at 10 million cells per spreadsheet. That's the hard ceiling, but recalculation overhead hits long before you get there.

Each formula cell is a node in Sheets' dependency graph. Every time any input changes, Sheets walks that graph to determine what recalculates. A column with 8,000 individual IFERROR(VLOOKUP()) formulas creates 8,000 dependency nodes. The equivalent ARRAYFORMULA creates 1.

On a 12,000-row GL export with 6 classification columns - 72,000 individual formula cells vs. 6 ARRAYFORMULA cells - recalculation after a cell edit dropped from roughly 4.2 seconds to under 0.8 seconds. That's the difference between a model that feels responsive and one that makes you wait through every edit.

The tradeoff worth knowing: ARRAYFORMULA recalculates the entire column even when only one row changes. For datasets above 100,000 rows with complex nested formulas, that can bite you. At 10,000-50,000 rows - the typical FP&A range - ARRAYFORMULA wins almost every time.

Where AI Shortens the Trial-and-Error

The syntax is learnable in an afternoon. The hard part is constructing the right formula for a specific situation - multi-tab lookup, concatenated keys, fiscal calendar mapping, and an empty-row guard, all nested correctly in one shot.

ModelMonkey handles this inside Google Sheets: describe what you need ("tag each GL row with the fiscal quarter from the Fiscal Calendar tab, return blank if the date column is empty"), and it writes the ARRAYFORMULA. It's not a replacement for understanding the function, but it cuts the back-and-forth that turns a 15-minute task into an hour of debugging nested parentheses. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.