Excel SUM formula to total a column, rows or only visible cells

This tutorial demonstrates how to calculate sums in Excel using the AutoSum feature and custom SUM formulas for columns, rows, and selected ranges. It also covers summing only visible cells, calculating running totals, summing across multiple sheets, and troubleshooting common SUM formula errors.

Need a quick sum? Select the cells and check the status bar in the bottom-right corner of your Excel window:

For more permanent results, use Excel's SUM function. Even beginners will find it easy to understand.

Key Topics:

• Basic Arithmetic Sums
• The SUM Function
• AutoSum
• Summing Columns
• Summing Rows
• Using the Total Row Feature in Tables
• Summing Visible Cells Only
• Calculating Running Totals
• Summing Across Worksheets
• Conditional Sums (SUMIF, SUMIFS)
• Troubleshooting SUM Formula Errors

Basic Arithmetic Sums:

For quick totals of a few cells, use the plus sign ( ): =1 2 3 or =A1 C1 D1

For larger datasets, the SUM function is more efficient.

The SUM Function:

The SUM function adds values: SUM(number1, [number2], ...) It accepts up to 255 numbers, ranges, or cell references. Examples: =SUM(A1:A100), =SUM(A1, A2, A5), =SUM(1,5,-2), =SUM(A2:A4, A8:A9), =SUM(A2:A6, A9, 10).

SUM is often used within larger formulas. For example, combining SUM with IF for conditional calculations: =IF(AND($B2"", $D2""), SUM($B2:$D2), "Value missing")

AutoSum:

For quickly summing a column, row, or adjacent cells, use AutoSum (Home tab > Editing group) or the shortcut Alt =. Excel usually guesses the correct range; adjust if needed.

Summing Columns:

Use =SUM(B2:B8) to sum cells B2 to B8. To sum an entire column (with a variable number of rows), use =SUM(B:B). Avoid placing this formula within the column being summed to prevent circular references. To exclude header rows, use =SUM(B:B)-SUM(B1:B3) or specify a large range like =SUM(B2:B1048576) (Excel 2007 and later).

Summing Rows:

Similar to columns, use =SUM(B2:D2) for a row. To sum multiple rows individually, drag down the formula using relative or mixed cell references. For a whole row, use =SUM(2:2). Again, avoid circular references. To exclude columns, subtract the irrelevant columns: =SUM(2:2)-SUM(A2:B2).

Using the Total Row Feature in Tables:

Excel tables offer a convenient Total Row feature. Convert a range to a table (Ctrl T), then enable the Total Row (Design tab > Table Style Options). Select "Sum" from the dropdown in the Total Row to sum each column. This automatically uses SUBTOTAL, which ignores hidden rows. For sums including hidden rows, use a regular SUM function.

Summing Visible Cells Only:

Use the SUBTOTAL function: SUBTOTAL(function_num, ref1, [ref2], ...) Use 109 for SUM, ignoring hidden and filtered rows. AutoSum also works with filtered data, automatically inserting SUBTOTAL.

Calculating Running Totals:

Use a SUM formula with a mix of absolute and relative references: =SUM($B$2:B2). Copy this down to calculate cumulative sums.

Summing Across Worksheets:

Use 3-D references: =SUM(Jan:Apr!B6) or =SUM(Jan:Apr!B2:B5).

Conditional Sums:

Use SUMIF for single criteria and SUMIFS for multiple criteria.

Troubleshooting SUM Formula Errors:

• #NAME? error: Check for spelling mistakes in the SUM function.
• Numbers not added: Check for numbers formatted as text. Use the warning triangle to convert to numbers.
• SUM returns 0: Check for circular references.
• SUM returns a higher number: Use SUBTOTAL to ignore hidden rows.
• SUM formula not updating: Ensure Calculation Mode is set to Automatic (Formulas tab > Calculation Options).

Downloadable practice workbook available.

The above is the detailed content of Excel SUM formula to total a column, rows or only visible cells. For more information, please follow other related articles on the PHP Chinese website!