To add the values in an array or range in Excel, use =SUM(A2:A10) when the numbers are in A2:A10. In Microsoft 365 for Windows, SUM can also total the results of array calculations, such as multiplying matching values in two ranges; the right formula depends on whether you are adding cells directly or calculating first.
What “SUM array” means in Excel
People use “Excel SUM array” to mean a few related things. They may want to add a range of cells, sum the results of an operation applied to matching cells, add values that meet a condition, or understand an older array formula entered with Ctrl+Shift+Enter. These tasks are connected, but they do not all require the same formula.
An array is a collection of values that Excel can process together. A cell range such as A2:A10 can serve as an array, and an expression can produce one too. For example, multiplying a quantity array by a price array produces an array of row-by-row products. SUM then adds those results into one number.
In current Microsoft 365 Excel, ordinary formulas can generally use dynamic-array calculations without the old special entry keystroke. However, SUM returns a single total, not a spilled list. That distinction is useful: a formula may calculate an array internally even though its visible result is just one cell.
Sum a range with SUM
When you simply need to total cells, the SUM function is the clearest choice. With numbers in A2:A6 equal to 10, 20, 30, 40, and 50, and the data stored in A2:A6, enter =SUM(A2:A6). The result is 150. SUM accepts individual cell references, multiple ranges, and numbers typed directly into the function.
A range can include blank cells; blanks do not change the total. SUM also ignores text in a referenced range, which is often convenient when labels and numbers are mixed. Be aware that text that looks like a number may not always behave as a number, especially if it was imported or stored as text. If a total seems too low, check the source cells rather than assuming the formula is broken.
To total separate ranges, the layout might have January values in B2:B10 and February values in D2:D10. Use =SUM(B2:B10,D2:D10) to add both ranges. This returns the sum of all numeric cells in the two ranges; the comma separates function arguments and is not an arithmetic operator.
Multiply matching arrays, then add the results
A common reason to search for a SUM array formula is a weighted total, such as quantity multiplied by unit price for each row. Suppose quantities are in B2:B4 as 2, 3, and 4, and unit prices are in C2:C4 as 5, 7, and 6. With those values in the stated ranges, enter =SUM(B2:B4*C2:C4). Excel multiplies corresponding rows (2×5, 3×7, and 4×6) and adds the products, returning 55.
That formula relies on the arrays having compatible dimensions and corresponding row order. B2 is paired with C2, B3 with C3, and B4 with C4. If one range is longer, begins on a different row, or includes a heading while the other does not, the formula may return an error or calculate unintended pairings. Check both range boundaries before diagnosing the arithmetic.
In Microsoft 365, enter this kind of formula with Enter. In older desktop Excel versions, formulas that perform array operations inside SUM could require Ctrl+Shift+Enter to tell Excel to calculate the expression as an array formula. When Excel adds the legacy formula correctly, it displays braces around it in the formula bar; do not type those braces yourself. On Windows, Ctrl+Shift+Enter is the legacy keystroke. On Mac, the corresponding legacy entry shortcut is Control+Shift+Return. The precise behavior depends on the Excel version, so current Microsoft 365 users should normally try Enter first.
Sum values that meet a condition
For a single condition, SUMIF is usually easier to read than building an array expression. Suppose A2:A6 contains region names and B2:B6 contains sales, with North appearing on rows whose sales are 100, 80, and 120. With the region labels in A2:A6 and sales in B2:B6, enter =SUMIF(A2:A6,"North",B2:B6). The result is 300. SUMIF checks the first range for the criterion and adds the corresponding cells from the sum range.
SUMIF is a good fit when the criterion is straightforward, such as one text label or one numeric comparison. The criterion can also refer to a cell, which is helpful if a user should be able to change the region without editing the formula. For example, if the desired region is entered in D2, use =SUMIF(A2:A6,D2,B2:B6), assuming region names are in A2:A6, sales are in B2:B6, and D2 contains the chosen region. It returns the total for the region named in D2.
For multiple conditions, SUMIFS is often the most maintainable option. If A2:A6 contains regions, B2:B6 contains product names, and C2:C6 contains sales, use =SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Desk") to add sales where the region is North and the product is Desk. The result depends on the data in those rows. The sum range is listed first in SUMIFS, followed by each criteria range and criterion pair.
You can also express conditions as arrays. If A2:A6 contains region names and B2:B6 contains sales, use =SUM((A2:A6="North")*B2:B6). Excel tests each region, turns the matching TRUE results into 1 and nonmatching FALSE results into 0 during multiplication, and adds only the sales values paired with 1. For the example above, the result is 300. This style can be useful when conditions are part of a larger calculation, but SUMIF or SUMIFS is generally clearer for ordinary conditional totals.
SUM versus SUMPRODUCT
SUMPRODUCT multiplies corresponding array entries and then sums the products, which makes it a natural alternative for weighted totals. With quantities 2, 3, and 4 in B2:B4 and prices 5, 7, and 6 in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4). The result is 55, the same as the SUM array example. SUMPRODUCT has long been available across Excel versions and can be useful when sharing a workbook with people using older releases.
There is no need to use SUMPRODUCT merely because the data is in a range. For a plain total, SUM is more direct. For conventional one- or multiple-criteria totals, SUMIF and SUMIFS usually make the intent easier to recognize. SUMPRODUCT is handy when you need a compact calculation that combines multiplication with logical tests, or when compatibility with older Excel matters.
Array formulas can also be harder to audit when they contain several conditions, conversions, or nested calculations. If a formula is becoming difficult to explain, consider adding a helper column that calculates each row’s contribution and then summing that column. A few extra cells can make a workbook easier for another person to inspect and maintain.
Dynamic arrays and legacy array entry
Dynamic-array support changed how many array formulas are entered, but it did not make every array-related formula behave identically. In Microsoft 365 Excel, a formula that passes an array expression into SUM can generally be entered normally. Functions such as FILTER can also return arrays that spill into neighboring cells, while SUM reduces its input to a single total. A spilled result and an array calculation inside SUM are therefore not the same thing.
Older Excel versions used Ctrl+Shift+Enter for certain formulas that calculated over arrays. These are often called CSE formulas. A saved CSE formula can still appear in an older workbook, and Excel may preserve its legacy behavior. If a formula copied from an old file returns an unexpected result, check whether it was designed as a CSE formula and which Excel version is opening it.
Do not add Ctrl+Shift+Enter automatically to every formula containing a range. In current Microsoft 365, begin by entering a formula normally. If compatibility with an older Excel release is important, confirm that release’s behavior and test the workbook in the version used by its recipients.
Common errors and ways to troubleshoot
A formula can be mathematically sound and still fail because its inputs are not arranged as expected. Work through these common checks:
- Unequal range sizes: Array multiplication should pair entries consistently. Confirm that both ranges start and end on the intended rows.
- Numbers stored as text: Imported data can look numeric while remaining text. SUM may skip text in a referenced range, and arithmetic array expressions may produce an error when text is used as a number.
- Headers included by mistake: Start calculation ranges below the header row unless the formula is specifically designed to handle it.
- Unexpected zero: For SUMIF or SUMIFS, check spelling, extra spaces, and whether the criteria range aligns with the sum range. A label such as North followed by a trailing space may not match North.
- #VALUE! errors: Look for text or error values in cells used in arithmetic, as well as mismatched array dimensions.
- Wrong result after copying: Relative references shift when a formula is copied. Check whether ranges should be locked with dollar signs, such as $B$2:$B$6, before filling the formula elsewhere.
Use Excel’s formula bar to inspect the exact references, and temporarily select a small part of the data to verify the expected row-by-row pairings. If a result is still unclear, calculate one or two products by hand. For a weighted total, confirming the individual products often reveals a shifted range or an unexpected text value faster than rewriting the formula.
Choosing the right approach
Choose the simplest function that describes the task. This makes the workbook easier to check and reduces the chance that a later edit will silently change the meaning of the calculation.
- Add cells or ranges: Use SUM.
- Add values matching one condition: Use SUMIF.
- Add values matching several conditions: Use SUMIFS.
- Multiply corresponding arrays and add the products: Use SUMPRODUCT for broad compatibility, or SUM around the array calculation in current Excel.
- Build a more complex calculation from row-by-row conditions: An array expression may fit, but consider whether helper columns would make the logic clearer.
Also consider who will open the workbook. Microsoft 365 has modern array behavior, but a workbook may be used in an older Excel release or another spreadsheet program. SUM, SUMIF, SUMIFS, and SUMPRODUCT are established Excel functions, while newer dynamic-array features are not supported in every older version. Avoid relying on a formula’s newest behavior when the file must work in an older environment unless you have verified compatibility.
Frequently Asked Questions
Do I need Ctrl+Shift+Enter for a SUM array formula?
Usually not in Microsoft 365 Excel. Enter a formula normally first. Ctrl+Shift+Enter is associated with legacy array formulas in older Excel versions; use it only when the version and formula require it.
Can SUM add the results of multiplying two ranges?
Yes. If the ranges have matching dimensions, SUM can total their row-by-row products in current Excel. SUMPRODUCT is another established option for that calculation and can be more compatible with older Excel versions.
Why does SUM ignore some values that look like numbers?
Those cells may contain text rather than numeric values. Check the cell contents and how the data was imported. Text in a referenced range is generally ignored by SUM, while text used in arithmetic may cause an error.
Is SUMPRODUCT better than SUM?
Neither is universally better. SUM is the clearest choice for adding values directly. SUMPRODUCT is useful for paired multiplication and array calculations, especially when older-version compatibility is a concern.
Does a SUM array formula spill results into nearby cells?
No. SUM returns one total in its formula cell. Other formulas can return arrays that spill, but a total calculated by SUM is a single value.
Related
- Excel WEEKDAY: Return a Day Name from a Date
- Excel NOW as Text: Show the Current Date and Time
- Excel Current Year: How to Get It with NOW and TODAY
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells.…
- Different Labels in Word: How to Make Each Label UniqueTo create different labels on one sheet in Microsoft Word, start a label mail merge, connect a recipient…
- Excel Formula for Tomorrow’s Date: TODAY()+1 ExplainedIn Excel, use =TODAY()+1 to return tomorrow’s date. Enter it in a cell and format that cell as…
- LEFT, RIGHT and MID Formulas in Excel: How to Use ThemUse LEFT to return characters from the start of a text value, RIGHT to return them from the…
- Excel Consolidate Text: Formulas, Flash Fill, and Power QueryTo consolidate text from Excel cells, use TEXTJOIN when you want a separator and control over blank cells,…
- Power Query If Function: Syntax, Examples, and TipsPower Query’s if is a conditional expression, not a function: write if condition then value_if_true else value_if_false. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.