SUM function

SUM adds numeric values in a range or multiple ranges, ignoring text and empty cells, and returns the total sum.

=SUM(number1, ...)

Generate a SUM formula

Describe what you need. The generator will reach for SUM where SUM is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SUM reads its arguments
number1requiredSUM →
ArgumentRequiredDescription
number1RequiredThe first range or cell to sum; can be a single cell, range like A1:A10, or multiple ranges separated by commas.
...RepeatingAdditional ranges or cells to sum; SUM can handle dozens of arguments to combine values from non-contiguous areas.

Returns

A single number representing the sum of all numeric values in the provided range(s).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Sum total units sold across all orders

Order IDRegionRepUnitsUnit PriceRevenueOrder Date
1001NorthAlice5452252024-01-15
1002SouthBob3601802024-01-16
1003NorthAlice8453602024-01-17
1004EastCharlie2751502024-01-18
1005WestDiana6503002024-01-19
1006SouthBob4602402024-01-20
=SUM(D2:D7)

Result: 28

The formula adds all unit quantities from column D (rows 2–7): 5 + 3 + 8 + 2 + 6 + 4 = 28. SUM automatically ignores text headers and only processes numeric values.

2. Sum total revenue for the month

Order IDRegionRepUnitsUnit PriceRevenueOrder Date
1001NorthAlice5452252024-01-15
1002SouthBob3601802024-01-16
1003NorthAlice8453602024-01-17
1004EastCharlie2751502024-01-18
1005WestDiana6503002024-01-19
1006SouthBob4602402024-01-20
=SUM(F2:F7)

Result: 1455

SUM totals all revenue values from the six orders: 225 + 180 + 360 + 150 + 300 + 240 = 1455. This is the gross sales figure for the period.

3. Sum revenue from multiple non-contiguous regions

Order IDRegionRepUnitsUnit PriceRevenueOrder Date
1001NorthAlice5452252024-01-15
1002SouthBob3601802024-01-16
1003NorthAlice8453602024-01-17
1004EastCharlie2751502024-01-18
1005WestDiana6503002024-01-19
1006SouthBob4602402024-01-20
=SUM(F2:F3,F5:F6)

Result: 945

SUM adds multiple separate ranges by separating them with commas. Here it sums rows 2–3 (North and South) plus rows 5–6 (West), skipping row 4: 225 + 180 + 300 + 240 = 945.

Common errors

Which SUM error are you seeing?
SUM returned an error#VALUE!
Ensure the range contains only numeric values, or exclude columns with text headers and labels from the sum.
#REF!
Update the formula to reference valid cells; for example, change =SUM(A1:A10) to =SUM(A1:A8) if rows 9–10 were deleted.
#NAME?
Check the spelling of SUM and ensure it matches your spreadsheet application's function name; use autocomplete to verify.
ErrorWhy it happensHow to fix it
#VALUE!One or more cells in the range contain text that SUM cannot convert to a number, such as 'Alice' or 'North'.Ensure the range contains only numeric values, or exclude columns with text headers and labels from the sum.
#REF!The formula references cells that have been deleted, or the range endpoints no longer exist due to row or column removal.Update the formula to reference valid cells; for example, change =SUM(A1:A10) to =SUM(A1:A8) if rows 9–10 were deleted.
#NAME?SUM is misspelled or not recognized; for example, =SUMM(...) or =SUM() with missing syntax, or the function name is not valid in your application.Check the spelling of SUM and ensure it matches your spreadsheet application's function name; use autocomplete to verify.

Tips and when to use something else

  • SUM ignores text, logical values (TRUE/FALSE), and empty cells, treating them as zero; it processes only numbers.
  • Use SUMIF or SUMIFS when you need to sum values that meet a specific condition, such as summing revenue for a single rep or region.
  • For weighted sums or products, use SUMPRODUCT instead; for example, =SUMPRODUCT(D2:D7, E2:E7) multiplies units by unit price then sums.
  • On filtered data, use SUBTOTAL to sum only visible rows; SUM will include hidden cells, which may give misleading results.

Related functions

SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.SUBTOTALReturns a subtotal of numeric values in a range, optionally excluding hidden rows and nested SUBTOTAL functions.

Frequently asked questions

How do I sum only values that meet a certain condition?
Use SUMIF or SUMIFS instead of SUM. For example, =SUMIF(B2:B7,"North",F2:F7) sums revenue only for North region orders. SUM alone cannot filter by criteria.
Why does my SUM formula include hidden rows?
SUM sums all cells in a range, including those hidden by filtering or row hiding. If you want to sum only visible cells, use SUBTOTAL(9, range) instead, where 9 is the code for sum-visible-only.
Can I sum ranges from different sheets?
Yes. Use the syntax =SUM(Sheet1!A1:A10, Sheet2!A1:A10) to sum ranges from multiple sheets. Separate each sheet reference with a comma, and include the sheet name followed by an exclamation mark before the cell range.
What is the maximum number of arguments SUM can accept?
Most spreadsheet applications allow dozens of arguments (typically 254 or more), so you can sum many ranges in a single formula. However, for readability, consider grouping related sums or using SUMIF for large datasets.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17