Google Sheets does not array-evaluate inside a scalar function: when you need ARRAYFORMULA, and when it doesn't help

Here is a rule that decides whether a whole class of Excel and LibreOffice formulas survives a move to Google Sheets, stated as our executed runs show it: when an array expression is handed to a function that returns a single value, Google Sheets uses only the first element of that array — unless the expression is wrapped in ARRAYFORMULA, or unless the outer function is one that takes arrays natively, such as SUMPRODUCT or FILTER. Excel and LibreOffice Calc expand the array instead, and compute over all of it.

That difference is not an error message. In six of the nine divergent rows below, Google Sheets returns a value rather than an error; in four of those six it returns a number, and the number is wrong. Our run of =SUM(SUMIFS(C2:C6,A2:A6,{"North","South"})) returned 140 in Google Sheets against 220 in LibreOffice Calc 25.8.7.3. Nothing about 140 looks broken. It is the North subtotal on its own, sitting in a cell that is supposed to hold North plus South.

Executed results: nine formulas that change value or break

Every row is a formula from one of our how-to recipe pages. The Excel column is Microsoft’s documented behaviour, which is also the value our corpus expects; the Google Sheets column is the value that engine returned on 2026-08-30; the LibreOffice column is Calc 25.8.7.3. The last column is a rewrite executed in Google Sheets on the same run — the formula that gets the right answer there, quoted exactly as we ran it.

Inputs, per recipe. The two SUMIFS rows read A1:C1 = Region, Product, Amount with A2:C6 = North/Widget/100, South/Widget/50, East/Widget/25, North/Gadget/40, South/Gadget/30. The SUMIF row reads A2:B6 = East/100, West/50, North/25, East/10, South/5. The LARGE row reads A2:A6 = 60, 90, 70, 85, 95. The first LOOKUP row reads A2 = apple, A3 = banana, A5 = cherry, with A4 and everything below A5 blank. The second reads A2:B5 = apple/10, banana/20, apple/30, cherry/40 with D2 = apple. The XLOOKUP row reads A2:C6 = North/Q1/100, North/Q2/250, South/Q2/300, South/Q1/150, North/Q3/400. The reverse-a-string row takes literals, and the digit-extraction row reads A2 = Order #4092 (qty 15).

Read down the Google Sheets column and the pattern is the rule, case by case. The SUMIFS row totals only the North rows, 100 + 40 = 140: the first element of {"North","South"} and nothing else. The SUMIF row totals only the East rows, 100 + 10 = 110. =AVERAGE(LARGE(A2:A6,{1,2,3})) averages a single number, LARGE(A2:A6,1) = 95, so it reports the top score dressed up as the average of the top three. MID("abc",SEQUENCE(3,1,3,-1),1) yields MID("abc",3,1) = "c" alone, and TEXTJOIN joins that one character. In the digit-extraction row the first character of Order #4092 (qty 15) is O, "O"*1 is an error, IFERROR turns it into an empty string, and the cell comes back blank.

The three remaining rows are the ones that fail loudly rather than quietly, and they share a shape: the array is built by an operator applied across a range — 1/(A2:A100<>""), 1/(A2:A5=D2), and the concatenation A2:A6&"|"&B2:B6. Those expressions do not expand into arrays on their way into LOOKUP and XLOOKUP, and the measured results are #N/A, #N/A and #VALUE!. Our readback records the error code a cell holds, not the hover text beside it, so those three codes are the measurement and the explanation above is our reading of the same rule that produces the six quiet rows.

What Google’s own help says ARRAYFORMULA is for

We fetched Google’s ARRAYFORMULA help article on 2026-08-30. Its one-line description is the rule from the other side: the function “enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.” That second clause is the whole subject of this page — the use of non-array functions with arrays is presented by Google as something you switch on, not as the default. The article’s argument description is equally telling: array_formula is “a range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.” Our three loud rows are exactly that middle category, a mathematical expression over ranges of the same size. Google also notes that many array formulas expand into neighbouring cells automatically, “obviating the explicit use of ARRAYFORMULA”, and that Ctrl+Shift+Enter inserts the wrapper for you.

What the help article does not say anywhere is that a SUMIFS criteria argument is excluded. That gap is what our executed rows fill in.

The exception: SUMIFS and SUMIF criteria arrays, where ARRAYFORMULA does not help

The SUM(SUMIFS(…,{"North","South"})) idiom is the standard Excel answer to “OR across one column”: hand the criteria argument an array constant, get one subtotal per value back, and let the outer SUM add them. It is documented brace syntax in all three products, and our LibreOffice run computes it. In Google Sheets it returns the first subtotal only, and adding the wrapper the rule would predict changes nothing:

Wrapped and unwrapped return the same value, twice over. On this evidence SUMIFS’s criteria argument is not array-expanded in Google Sheets even inside ARRAYFORMULA, and the same goes for SUMIF: the =SUM(SUMIF(A2:A6,{"East","West"},B2:B6)) row above returned 110, and the rewrite our corpus records for it is not a wrapper at all but a different function.

SUMPRODUCT is the portable form. It takes arrays as a matter of signature, so nothing needs switching on: =SUMPRODUCT((A2:A6="North")+(A2:A6="South"),C2:C6) returned 220 in the Google Sheets run and 220 in LibreOffice Calc 25.8.7.3, against a documented 220. The + between two boolean comparisons is the OR; multiply instead of adding and you get AND. The same recipe’s two-dimensional cases are built the same way and also matched in both engines: =SUMPRODUCT((A2:A4="South")*(B1:D1="Q2")*B2:D4) returned 50, and =SUMPRODUCT(((A2:A4="South")+(A2:A4="East"))*(B1:D1="Q2")*B2:D4) returned 130.

Our separate function corpus, executed in Google Sheets on 2026-08-29, backs the general point that the classic SUMPRODUCT array idioms are safe there. =SUMPRODUCT(--(A1:A5>2)), the double-unary-minus counting trick, returned 3 as documented; =SUMPRODUCT((A1:A5>2)*B1:B5), the SUMIF replacement, returned 120; and =SUMPRODUCT(A1:A3,B1:B3) returned 140. Sheets is not refusing to do array arithmetic. It is refusing to do it in one specific place: inside the argument list of a function that returns a scalar.

FILTER makes the same point from the other direction. It is array-native, and in the same function corpus =FILTER({1,2,3,4},{1,0,1,0}) — two brace arrays, no ARRAYFORMULA anywhere — read back 1, 3, matching its documented result, while =FILTER(A1:A5,B1:B5>2) read back c, d, e. A brace array handed to FILTER is used in full; the identical brace array handed to SUMIFS is truncated to its first element. That is the whole rule in two rows.

A caution on the reverse reading: an array-native outer function is not a promise that everything about it matches. Our =FILTER(A1:A3,B1:B3>100,"none") case returned #N/A rather than the documented none in the same Sheets run, and SORT’s third argument means something different in Sheets than in Excel. Those are separate divergences with their own writeups — see FILTER with no results and the SORT section of which LibreOffice version you need for VSTACK, TEXTSPLIT, TAKE and DROP.

Which idioms break silently on the way into Sheets

Silently is the word that matters. A workbook full of #NAME? announces itself; a workbook where four cells quietly hold the first subtotal instead of the total does not. Of the nine divergent rows above, six return a value Sheets is perfectly happy with, and four of those six are plain numbers that will sum, chart and reconcile like any other number. Before you move a file, search the formula text for these shapes.

A brace array in a criteria argument. Anything matching SUMIFS(…,{ or SUMIF(…,{ — and by extension the COUNTIFS/AVERAGEIFS family, which we have not measured — is the highest-risk pattern on this page, because it is the one ARRAYFORMULA does not fix. Rewrite it as SUMPRODUCT with added boolean tests before the migration, not after. If the wanted values live in cells rather than in braces, the recipe page’s note points at =SUMPRODUCT(SUMIFS(C2:C6,A2:A6,E2:E3)) instead — that one is written guidance on our side, with no executed case behind it.

An array constant as the k of LARGE or SMALL. =AVERAGE(LARGE(range,{1,2,3})) is the canonical “average the top three” formula and it degrades to “the top one”. Wrapping the inner call fixes it: =AVERAGE(ARRAYFORMULA(LARGE(A2:A6,{1,2,3}))) returned the documented 90.

Arithmetic or concatenation applied across a whole range. 1/(range<>""), 1/(range=value) and rangeA&"|"&rangeB are the building blocks of the last-value, last-match and two-criteria-lookup idioms. These fail with an error rather than a wrong number, so they are the easy ones to find; wrap the expression, or the whole formula, in ARRAYFORMULA. Both LOOKUP rows and the XLOOKUP row were fixed that way or by a native rewrite in our run.

Per-character work built on SEQUENCE and MID. Reversing a string, extracting digits, and anything else that walks a string one character at a time collapses to the first character. Wrapping the MID call restores it; but where Sheets has a purpose-built function, that is usually the better answer, and our corpus records =REGEXREPLACE(A2,"[^0-9]","") returning 409215 for the digit-extraction case — shorter, and no wrapper to forget.

Do not reach for IFERROR. The three loud rows are the ones a migration can actually catch. Wrapping them in IFERROR converts the only visible symptom into a blank cell, which puts them in the same category as the quiet four. Rewrite the formula instead.

A practical note on where the wrapper goes. Our executed alternatives put ARRAYFORMULA in two different places: around the whole formula (=ARRAYFORMULA(LOOKUP(…))) and around just the array-producing sub-expression (=AVERAGE(ARRAYFORMULA(LARGE(…))), =XLOOKUP("North|Q2",ARRAYFORMULA(A2:A6&"|"&B2:B6),C2:C6)). Both worked in the cases we ran. Wrapping the inner expression is the more surgical choice, since it leaves the outer scalar function to do its scalar job.

Where each of these formulas is explained in full

Every row in the main table is the worked example of a how-to page, each with its own inputs, alternatives and per-engine notes: how to sum with multiple criteria (SUMIFS), how to SUMIF with OR criteria, how to average the top N scores, how to find the last value in a column, how to look up the last matching value, how to look up a value with two criteria, how to reverse a text string and how to extract numbers from text in a cell.

Two neighbouring guides cover the other ways Google Sheets and LibreOffice diverge on arrays and text-joining: which LibreOffice version you need for VSTACK, HSTACK, TEXTSPLIT, TAKE and DROP, which maps the array-shaping functions across four Calc builds and records which of them Sheets runs at all, and CONCAT takes exactly two arguments in Google Sheets, which is the same species of problem — a function present in both engines with a narrower argument contract in Sheets — except that CONCAT fails loudly with #N/A where the rule on this page mostly fails quietly. For the function-by-function picture, see Excel vs Google Sheets formulas.

Honest limits

The Excel column is Microsoft’s documented behaviour as recorded in our test corpus, and it is what every case is measured against. We do not run Excel, and no value in that column is a measurement.

The Google Sheets columns hold executed output from dated runs rather than from a pinned version, because Sheets has no version number to pin. Every recipe value on this page comes from the run labelled Google Sheets (Drive import, 2026-08-30): the corpus was written to .xlsx with no cached results, imported to Google Drive, recalculated by Google’s engine, and exported back for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. The SUMPRODUCT and FILTER rows drawn from our separate function corpus come from the 2026-08-29 run of that corpus and are labelled as such above. Google could change any of this tomorrow without changing a version number.

The LibreOffice column is executed output from Calc 25.8.7.3 only. This page makes no claim about older Calc builds.

The alternatives in the last column were executed in Google Sheets alone. They are scoped to that engine in our corpus on purpose — ARRAYFORMULA and REGEXREPLACE are not LibreOffice functions, and the harness therefore skips those checks for Calc, which is why the LibreOffice cell of every such row on those how-to pages reads n/a rather than a number. There is no measurement of these rewrites outside Google Sheets on this page.

Three further limits. First, the last-matching-value rewrite =XLOOKUP(D2,A2:A6,B2:B6,,0,-1) reads one row further than the original =LOOKUP(2,1/(A2:A5=D2),B2:B5); row 6 is empty in that recipe’s inputs, and both returned 30. Second, six of the Sheets checks cited on this page carry a provenance note. They were written into the .xlsx with plain function names, while the LibreOffice reference run executed the _xlfn. storage form of the same formula, so the two runs did receive different input files, even though the formula text is identical. Third, this page generalises a rule from nine executed recipe rows and a handful of function-corpus cases. It is consistent across all of them, and consistent with what Google documents ARRAYFORMULA to be for, but we have not enumerated every scalar function or every array-producing expression. See our methodology for how recalculation is proven rather than assumed.

Check before you migrate