SUM is the first function most Excel users learn and remains one of the most-used functions throughout any Excel career. The syntax is dead simple: =SUM(range) adds up all numbers in the range and returns the total. =SUM(A1:A100) sums everything in cells A1 through A100. =SUM(A1, B1, C1) sums three individual cells. SUM ignores text and blank cells automatically, so you don't have to clean up data before summing.

The keyboard shortcut for AutoSum (Alt + = on Windows, Cmd + Shift + T on Mac) is the single most useful Excel shortcut. Select an empty cell at the bottom of a column of numbers, press Alt + =, and Excel automatically inserts =SUM(range) covering the contiguous numbers above. Press Enter to confirm. This pattern works for rows too — select the empty cell to the right of a row of numbers and Alt + = creates the row sum.

Beyond simple SUM, the function has features most users don't realize exist. Summing across multiple worksheets: =SUM(Sheet1:Sheet5!A1) sums cell A1 across Sheet1 through Sheet5. This is called a 3D reference and works for any range that exists in the same position across multiple sheets. Useful for monthly reports with one sheet per month and a summary sheet that totals across them.

SUM with conditions: SUM by itself doesn't filter. For conditional sums, you'd typically use SUMIF (single condition) or SUMIFS (multiple conditions). =SUMIF(B:B, "West", C:C) sums column C where column B equals "West". =SUMIFS(C:C, B:B, "West", A:A, ">="&DATE(2026,1,1)) adds a date constraint. SUM is the unconditional version; the IF variants add filtering.

SUM ignores errors in some scenarios and propagates them in others. By default, if any cell in the SUM range contains an error (#DIV/0!, #N/A, #VALUE!), the SUM returns that same error. To sum while ignoring errors, wrap in IFERROR or use AGGREGATE: =AGGREGATE(9, 6, A1:A100) where 9 is the SUM function code and 6 tells AGGREGATE to ignore errors. This is cleaner than nested IFERROR patterns.

SUMPRODUCT is the closest cousin of SUM and handles array operations that SUM alone can't. =SUMPRODUCT(A1:A10, B1:B10) multiplies corresponding elements (A1×B1, A2×B2, etc.) then sums the products. Useful for weighted averages, dot products, and conditional sums with complex Boolean logic.

This guide covers SUM syntax in detail, the AutoSum shortcut, summing across sheets, when to use SUMIF/SUMIFS vs. SUM, error handling, AGGREGATE for ignoring errors, SUMPRODUCT for array operations, common mistakes, and when other approaches like PivotTables or SUBTOTAL might be better than SUM.

AutoSum is Excel's most useful shortcut and worth ingraining as muscle memory. The Alt + = combination (Windows) or Cmd + Shift + T (Mac) creates a SUM formula with Excel auto-detecting the range. Excel looks above the active cell for contiguous numbers; if it finds none, it looks to the left. Press Enter to accept or modify the range first if Excel guessed wrong.

AutoSum works in multiple modes. Select a single empty cell at the bottom of a column → Alt + = → sums the column. Select an empty cell to the right of a row → Alt + = → sums the row. Select a 2D range with sum cells along the bottom and right edges → Alt + = → fills in row and column totals plus a grand total. This last pattern is particularly useful for building summary tables quickly.

For more complex AutoSum needs: select your data range plus the empty cells where sums should go. Alt + = fills in all the sums at once. This is faster than typing individual SUM formulas for each row/column total.

SUM with individual cell references vs. range references: =SUM(A1, A2, A3, A4, A5) and =SUM(A1:A5) produce the same result but the second is much shorter. For more than 3-4 cells, always use range references. The function can take up to 255 separate arguments, but you'll rarely need more than 2-3 (mixing different ranges or adding individual cells to a range total).

SUM and dates: SUM doesn't make sense with dates directly because dates are serial numbers internally — you'd get the sum of the serial numbers which is meaningless. For date-related calculations, use COUNTIF (count occurrences), AVERAGEIF (average date range as a number), or DATEDIF (compute date differences). SUM is for numeric quantities, not temporal values.

SUM with text that looks like numbers: SUM ignores text by default, so cells containing "100" (as text) are skipped even though you might expect them to be added. Convert text to numbers first using VALUE function or by using Paste Special > Multiply (multiply by 1 to coerce text to number). If you want to sum text-numbers without conversion, use SUMPRODUCT(--A1:A10) — the double negative coerces text to numbers during summation.

Summing across worksheets with 3D references is one of SUM's most useful but underused features. If you have a workbook with monthly sheets (Jan, Feb, Mar, ..., Dec) all structured identically, you can sum across all months with a single formula. =SUM(Jan:Dec!B5) returns the total of cell B5 across all 12 sheets. The same formula in different cells of a summary sheet gives you monthly-totaled metrics without complex formulas.

3D references work for ranges too. =SUM(Jan:Dec!B2:B10) sums B2:B10 across all 12 sheets. The sheets must be ordered correctly in the tab bar — Excel uses tab order to determine which sheets are included. Moving sheets to be outside the start/end range removes them from the sum; moving sheets inside adds them.

One caveat with 3D references: if any sheet between the start and end has the cell intentionally blank, that's fine. But if a sheet structure differs (different layout, different data), the 3D reference may produce unexpected results because it sums whatever exists at the specified position. For workbooks with sheets that have varying structure, consider using INDIRECT and a list of sheet names instead of 3D references.

SUM vs. SUMIF/SUMIFS: when to use which? Use SUM for unconditional totals of an entire range. Use SUMIF for single-condition filtered sums (=SUMIF(B:B, "West", C:C) sums C where B is "West"). Use SUMIFS for multi-condition filtered sums (=SUMIFS(C:C, B:B, "West", A:A, ">="&DATE(2026,1,1)) adds a date constraint). The argument orders differ between SUMIF and SUMIFS — SUMIF has criteria range first; SUMIFS has sum range first. This is a common source of errors.

SUM with errors: by default, SUM propagates errors. If A5 contains #DIV/0!, =SUM(A1:A10) returns #DIV/0!. To sum while ignoring errors, use AGGREGATE: =AGGREGATE(9, 6, A1:A10). The 9 is the function code for SUM; the 6 means "ignore errors". AGGREGATE supports many functions (AVERAGE, COUNT, MAX, MIN, etc.) and many ignore-options (errors, hidden rows, both, etc.). It's a more powerful version of SUBTOTAL that's worth knowing.

SUMPRODUCT deserves its own attention because it does things SUM can't. The function multiplies corresponding elements of arrays and sums the products: =SUMPRODUCT(A1:A10, B1:B10) returns A1×B1 + A2×B2 + ... + A10×B10. The most common use is weighted averages: if A has quantities and B has prices, SUMPRODUCT gives total revenue.

SUMPRODUCT with Boolean logic is even more powerful. =SUMPRODUCT((B1:B100="West")*(A1:A100>=DATE(2026,1,1))*C1:C100) sums C where both conditions are true. The Boolean arrays (B="West") and (A>=date) evaluate to 1 or 0; multiplying them creates the filter; multiplying by C gives the selectively-summed values. SUMIFS does similar work but with more constrained syntax — SUMPRODUCT is more flexible for OR logic, conditional multiplication, and complex array operations.

For OR logic in SUMPRODUCT: =SUMPRODUCT(((B1:B100="West")+(B1:B100="East"))*C1:C100) sums where B is West OR East. The plus sign creates OR; multiplication creates AND. Combining these gives you arbitrary Boolean expressions that SUMIFS can't express.

SUMPRODUCT performance: slower than SUMIF/SUMIFS on large datasets because it operates row-by-row. For multi-condition sums where SUMIFS works, prefer SUMIFS for performance. For complex Boolean logic, OR conditions, or array multiplications, SUMPRODUCT remains essential.

SUBTOTAL is another SUM variant worth knowing. =SUBTOTAL(9, A1:A100) sums A1:A100 — but ignores rows hidden by AutoFilter. This is the right tool for filtered tables: as you filter rows in/out, the SUBTOTAL value updates to reflect only visible rows. Regular SUM doesn't react to AutoFilter; SUBTOTAL does. The function code 9 = SUM; other codes provide AVERAGE (1), COUNT (2), MAX (4), MIN (5), and others.

For Excel Tables (Ctrl + T), Total Rows use SUBTOTAL automatically. When you enable Total Row in a Table (Table Design tab → Total Row checkbox), Excel inserts SUBTOTAL formulas that respect filter state. This is one of several reasons Excel Tables are preferable to plain ranges for structured data — the Total Row reacts intelligently to filters.

Common SUM mistakes and how to avoid them. Mistake 1: forgetting the equals sign. SUM(A1:A10) just displays the text "SUM(A1:A10)" in the cell — it's not a formula until you add =. Always start formulas with =.

Mistake 2: text-numbers not summing. SUM ignores text by default, so cells containing "100" (as text rather than number 100) are skipped. Either convert text to numbers (Data → Text to Columns → Date format) or use SUMPRODUCT(--A1:A10) — the double negative coerces text to numbers.

Mistake 3: SUM propagating errors. If any cell has #DIV/0!, the whole SUM returns #DIV/0!. Fix: use AGGREGATE(9, 6, range) to ignore errors, or wrap cells in IFERROR.

Mistake 4: hardcoded ranges that don't expand. =SUM(A1:A100) doesn't include new rows added beyond A100. Fix: use Excel Tables (Ctrl + T) so the range auto-expands, or use =SUM(A:A) for the entire column (slower but always current).

Mistake 5: SUM whole columns on huge workbooks. =SUM(A:A) works fine on small workbooks but slows down recalculation on workbooks with hundreds of thousands of rows. Specify actual ranges (A1:A100000) instead of whole-column references for performance.

Mistake 6: confusing SUM with COUNT. =SUM(A1:A10) returns the total of numerical values. =COUNT(A1:A10) returns how many cells contain numbers. =COUNTA(A1:A10) returns how many cells contain anything (numbers, text, dates, etc.). Use the right function for what you actually want.

Mistake 7: SUM with dates. =SUM(A1:A10) where A1:A10 contain dates returns the sum of the serial numbers — meaningless. For date analysis, use COUNTIF, DATEDIF, or DATE-based formulas. SUM is for numeric quantities.

SUM in Excel Tables works particularly well. After converting a range to a Table (Ctrl + T), structured references replace cell addresses with column names. =SUM(Sales[Amount]) sums the Amount column of the Sales Table. As you add rows, the Table auto-expands and the SUM stays current.

Total Row in Tables (Table Design tab → Total Row) inserts SUBTOTAL formulas that respect filter state. Click the dropdown in any Total Row cell to switch between None, Sum, Average, Count, etc. This is the cleanest pattern for summary aggregations in structured data.

For dashboards and reports, SUM is often paired with INDEX/MATCH or XLOOKUP for selective summing. =SUM(INDEX(Sales[Amount], MATCH("Q1",Sales[Quarter],0)):INDEX(Sales[Amount], MATCH("Q4",Sales[Quarter],0))) returns the sum of Amounts between quarters. This is more flexible than fixed range references but more complex than SUMIFS.

SUM for cumulative totals (running totals): use a SUM formula with a mixed-reference range. In C2, write =SUM($A$2:A2). This sums from A2 to A2 (just A2). Copy down: row 3 has =SUM($A$2:A3) which sums A2 through A3. Row 4 has =SUM($A$2:A4) summing A2 through A4. This creates a running total column showing cumulative amounts.

SUM with dynamic arrays in Microsoft 365: =SUM(FILTER(A:A, B:B="West")) returns the total of column A where column B equals "West". The FILTER produces a dynamic array of matching values; SUM aggregates them. This is an alternative to SUMIF for filtering, and the FILTER result can be referenced elsewhere as a spilled range.

Final note on SUM performance: SUM is extremely fast. Even =SUM(A:A) on a million-row column completes in milliseconds on modern hardware. Performance issues with SUM-based formulas almost always come from the surrounding logic (nested IFs, volatile functions, array formulas) rather than SUM itself. If a SUM-containing formula is slow, look at what wraps the SUM rather than blaming SUM.

SUM is the function most users learn first and use throughout their Excel career. The basics are simple — =SUM(range) sums everything — but the function has more capability than most people realize. AutoSum (Alt + =) is the single most useful Excel shortcut. 3D references make cross-sheet summing trivial. AGGREGATE handles error tolerance. SUBTOTAL works with filters. SUMPRODUCT extends summing to array operations.

For most users, the practical mastery sequence is: basic SUM first, learn AutoSum shortcut, get comfortable with SUMIF/SUMIFS for filtered sums, then add SUBTOTAL (for filtered tables) and AGGREGATE (for error tolerance) when those situations arise. SUMPRODUCT comes later for complex array work. Each builds on what came before, and each step is a real productivity gain for the right scenarios.

A practical productivity tip: get into the habit of selecting your data range and glancing at the status bar before writing a SUM formula. Excel displays the Sum, Average, and Count of any selected range in the bottom status bar by default. For quick "what's the total here?" checks, this avoids writing any formula at all. Right-click the status bar to add more metrics (Min, Max, Numerical Count). For repeated calculations, formulas are necessary; for one-off curiosity, the status bar is faster.

For users moving from Google Sheets to Excel (or vice versa): the SUM function is identical in both. =SUM(A1:A10) works the same way. SUMIF and SUMIFS work the same way. SUMPRODUCT works the same way. SUBTOTAL works the same way. The only meaningful difference is AutoSum shortcuts (Alt+= in Excel; no native Sheets shortcut, requires a custom keyboard macro). Cross-platform compatibility is one area where Excel and Sheets are genuinely interchangeable for SUM-based work.