Google Sheets Array Formula SUM: Examples and Fixes

To add values in Google Sheets, use =SUM(B2:B) when the numbers are in column B from row 2 down. Wrapping that formula in ARRAYFORMULA is unnecessary: SUM already returns one total. Use an array formula when you want Google Sheets to calculate multiple results from ranges, then use SUM if you need to combine those results into one number.

What SUM and ARRAYFORMULA Each Do

SUM adds numbers and returns a single result. ARRAYFORMULA lets a formula work with ranges or arrays rather than only one cell at a time, and can display multiple results in neighboring cells. The two functions can appear together, but they serve different purposes: SUM reduces values to a total, while an array formula can preserve a set of row-by-row results.

That distinction explains a common point of confusion. A formula such as =ARRAYFORMULA(SUM(B2:B)) returns one total, just as =SUM(B2:B) does. The outer function does not make the total more dynamic or cause one total to appear for every row. If your goal is a single column total, use the shorter SUM formula.

In Google Sheets, enter a formula in a cell and press Enter. If the formula produces an array of results, those results usually fill adjacent cells automatically. There is no need to press Ctrl+Shift+Enter to enter an array formula in Sheets.

Write a Basic Total for a Column or Range

Suppose a sales sheet has a header in B1 and sales amounts in B2 through B10. To add exactly those ten amounts, use =SUM(B2:B10). For example, if the values are 12, 18, 20, 25, 30, 15, 10, 8, and 12, the result is 150.

If new sales will be added below row 10, use an open-ended range instead. With sales in B2 and below, enter =SUM(B2:B) in a cell outside column B. The header is excluded, and future numeric entries in column B are included automatically. If the total formula is placed within the range it sums, it can include itself and create a circular dependency; keep it elsewhere or set a clear end row.

SUM adds numeric values and ignores blank cells and text entries in a referenced range. That is useful when a column contains a label or occasional notes, but it also means a number stored as text may not contribute as expected. Check whether the cell is actually numeric if the result is lower than expected.

When an Array Formula Is Useful for Summing

An array formula is useful when the work before the final sum must happen across several rows. For example, if B2:B10 contains item prices and C2:C10 contains additional fees, you can add each row’s price and fee and then total those combined amounts. With prices in B2:B10 and fees in C2:C10, use =SUM(ARRAYFORMULA(B2:B10+C2:C10)). If the row totals are 15, 22, and 13 in a three-row example, the formula returns 50.

The calculation has two stages: the array expression adds the values in corresponding rows, and SUM adds the resulting values together. In this example, B2 is paired with C2, B3 with C3, and so on. Both ranges should have the same height. If they do not, the formula may return an error or fail to produce the intended calculation.

If you want to see each row’s combined amount rather than only the grand total, put this formula in a clear output column. With identifiers in A2:A10, prices in B2:B10, and fees in C2:C10, use =ARRAYFORMULA(IF(A2:A10="",,B2:B10+C2:C10)). It returns a row total for each row with an identifier and leaves the output blank when the corresponding identifier is blank. The output cells below the formula must be empty so the results can expand.

For a single combined total, the row-by-row output is optional. Use the shorter formula that totals the array calculation directly. Keeping the intermediate row totals visible can still be helpful when you need to inspect the calculation or explain how the grand total was reached.

Sum Values That Meet a Condition

For a total based on one condition, use SUMIF; for multiple conditions, use SUMIFS. These functions are generally clearer than wrapping an ordinary sum in ARRAYFORMULA. For instance, if region names are in A2:A20 and sales are in B2:B20, the formula =SUMIF(A2:A20,"East",B2:B20) adds sales only for rows labeled East.

To total sales for East in April, assume regions are in A2:A20, dates are in B2:B20, and sales are in C2:C20. Use =SUMIFS(C2:C20,A2:A20,"East",B2:B20,">="&DATE(2026,4,1),B2:B20,"<"&DATE(2026,5,1)). The date criteria include April 1 and exclude May 1, so the test works for dates that include times as well as dates without times. Replace the year and month with the period you need.

In that formula, the amounts range comes first, followed by pairs of criteria ranges and criteria. Each criteria range must correspond row for row with the sales range. For a recurring report, you can refer to cells containing the region and start and end dates instead of typing those criteria into the formula.

Build a Running Total Down a Column

A running total is different from one grand total: it shows the accumulated sum at each row. If values are in B2:B10, this Google Sheets formula returns a cumulative result for each position in that range: =ARRAYFORMULA(SUMIF(ROW(B2:B10),"<="&ROW(B2:B10),B2:B10)). For values 5, 7, and 4 in the first three rows, the corresponding results are 5, 12, and 16.

The ROW function supplies the row numbers. For each row, SUMIF adds the values whose row numbers are less than or equal to the current row number. ARRAYFORMULA allows the criteria to be evaluated across the range, producing a list of cumulative results instead of just one result. Leave the cells beneath the formula clear for those results.

For a small sheet, individual formulas such as a sum from the first value through the current row can also be filled down. The array approach can reduce repeated formulas, but it is not automatically the best choice: a filled-down formula may be easier for another person to understand, and it lets you inspect or edit each row’s calculation separately.

Common Errors and How to Fix Them

When a formula does not return the expected sum, first check the input range and the cells it is supposed to include. These issues account for many confusing results:

The total is too small: A value may be stored as text, fall outside the selected range, or be excluded by a condition. Check a suspicious cell’s contents and verify the first and last rows in the formula.
An array result cannot expand: Clear the cells where the output needs to appear. Existing values or merged cells can block the array’s results and produce an expansion error.
The formula reports a circular dependency: A whole-column sum may include the cell containing the formula. Put the total outside the summed column or use a range that ends before the total cell.
Rows do not line up: For calculations that combine ranges, make sure the ranges start and end on corresponding rows. Adding B2:B10 to C3:C11 pairs values from different records.
A conditional total is zero: Check for extra spaces or inconsistent labels, and confirm that date cells contain dates rather than text that only looks like a date.

Also look for accidental differences in the values. East, east, and East followed by a space may not behave as the same clean category in every workflow. Standardizing labels can be more reliable than trying to compensate for inconsistent source data in every sum formula.

Choose the Simplest Formula That Fits

Use a direct SUM for an ordinary total, SUMIF or SUMIFS for a total with criteria, and ARRAYFORMULA when you want one formula to calculate a set of row-level results. Combine ARRAYFORMULA with SUM when the array calculation itself needs to be reduced to one overall number.

One total from a column: SUM with a bounded or open-ended range.
One total that meets criteria: SUMIF for one condition or SUMIFS for several.
A total based on row-by-row arithmetic: SUM around the array calculation.
A visible result for each row: ARRAYFORMULA with an expression that returns an array, placed where the output cells are free.

Open-ended ranges are convenient because they include later entries, but many large or complicated formulas over entire columns can make a busy spreadsheet slower. Use ranges sized to the data when practical, especially when several array calculations refer to the same columns. A bounded range is also easier to audit because its included rows are explicit.

How Google Sheets Differs from Excel

This guidance is for Google Sheets in a web browser. The Sheets function ARRAYFORMULA is not an Excel function, so an Excel user cannot paste a formula that contains ARRAYFORMULA into Excel unchanged. Both programs have SUM, but their array behavior and formula features differ. In Excel, newer Microsoft 365 versions can calculate many array expressions directly, while older versions may handle some array formulas differently.

The menu and keyboard workflows also differ. Google Sheets is edited in the browser and does not use Excel’s ribbon paths for spreadsheet commands. If a file is opened in Excel, check the formula there rather than assuming a Sheets-specific function will transfer. For a basic total, a simple SUM is the most portable choice; for array calculations, choose a formula supported by the spreadsheet program where the sheet will be used.

Frequently Asked Questions

Do I need ARRAYFORMULA to sum a column in Google Sheets?
No. If your values are in B2:B, =SUM(B2:B) returns their total. ARRAYFORMULA is useful when you need calculations across multiple rows, not as a requirement for adding a range.

Why does ARRAYFORMULA with SUM return only one number?
SUM reduces its input to one result. ARRAYFORMULA does not change that behavior. To return a result for each row, use an array calculation that produces multiple values and do not wrap the whole result in SUM.

Can I sum an entire column while ignoring the header?
Yes. If the header is in B1 and the data begins in B2, use =SUM(B2:B). Put the formula outside the range it totals to avoid including the formula cell itself.

How do I sum only rows that match a category?
Use SUMIF for one condition or SUMIFS for multiple conditions. For example, with categories in A2:A20 and amounts in B2:B20, =SUMIF(A2:A20,"East",B2:B20) totals the amounts for East.

Why is my array formula not filling down?
Check that the cells below the formula are empty and not merged, and confirm the formula actually returns an array. A formula wrapped around SUM returns one number, so there are no additional row results to fill down.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.