AGGREGATE is in Excel and LibreOffice (executed), but Google Sheets returns #NAME?

AGGREGATE is Excel’s heavy-duty aggregator: one function that can act as SUM, AVERAGE, COUNT, MAX, MIN, LARGE, MEDIAN and a dozen others (19 in all), with an options argument that tells it to ignore error values, hidden rows, or nested SUBTOTAL/AGGREGATE results. It is the clean way to total a column that contains the odd #DIV/0! without wrapping every cell in IFERROR. LibreOffice Calc has it too. Google Sheets does not — the name is unrecognised, so the formula comes back #NAME?.

A minimal example

Column A holds 10, 20, =1/0 (a #DIV/0! error) and 40. We want the total of the valid numbers, 70, without the error poisoning it. AGGREGATE function 9 (SUM) with option 6 (ignore errors) does exactly that:

70 is the documented-expected value in our test corpus, and it is what our harness got by recalculating the workbook in LibreOffice Calc 25.8.7.3 — identical in all four LibreOffice releases we test (24.2, 24.8, 25.2 and 25.8), so this is not a version gap. Google Sheets, executed the same way (a formula-only workbook through Drive import, 2026-08-29), returned #NAME?: it has no AGGREGATE function at all.

Why it happens

Excel introduced AGGREGATE in Excel 2010 and LibreOffice implemented a compatible version; Google Sheets never added it. There is no options-driven super-aggregator in Sheets, so the two things AGGREGATE bundles — ignore errors and ignore hidden/filtered rows — have to be rebuilt from other functions.

How to migrate to Google Sheets

Split the job by which AGGREGATE option you were using.

To ignore errors (options 6 and 7), strip the errors before aggregating:

=SUM(IFERROR(A1:A4,0)) returns 70 in Google Sheets — IFERROR maps each error cell to 0 across the range, and Sheets evaluates it as an array inside SUM. Swap SUM for AVERAGE, MAX or MIN as needed (for AVERAGE, map errors to "" rather than 0 so they are not counted).

To ignore hidden or filtered rows (options 1–7), use SUBTOTAL, which Google Sheets does have: =SUBTOTAL(9,A1:A4) sums only the visible rows. Note that SUBTOTAL does not skip errors, so if the range can contain both hidden rows and errors you need both tools: =SUBTOTAL(9,ARRAYFORMULA(IFERROR(A1:A4,0))).

Going the other direction — a Sheets workbook into Excel or LibreOffice — needs nothing: both apps read SUBTOTAL, SUM and IFERROR fine, and you can adopt AGGREGATE there if you want the single-call version back.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01); it computes AGGREGATE(9,6,A1:A4) as 70, the same as LibreOffice. Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.