How to Sum a Column in Excel: Fix SUM Errors and Understand SUBTOTAL vs AGGREGATE

Summing a column is one of the most common Excel tasks. Sales totals, expense totals, and headcount reports often start with a simple SUM formula. But totals can still go wrong when numbers are stored as text, filters hide rows, or error values are mixed into the range.

This guide explains how to sum a column correctly and how to choose the right function for filtered or messy data.

The Basic SUM Formula

The standard formula is simple: =SUM(B2:B100). You can also use AutoSum from the Home tab to let Excel select the range automatically.

SUM is best when you want to include every numeric value in the selected range.

Why SUM Returns 0

The most common reason is that the values look like numbers but are stored as text. This often happens after copying data from a website, exporting from an ERP system, or importing CSV files.

Text numbers may be left-aligned or show a warning indicator. Convert them to real numbers with Convert to Number, Text to Columns, or Paste Special Multiply by 1.

Why Filtered Totals Look Wrong

SUM includes hidden rows. If you filter a table down to 20 visible rows, SUM may still calculate all 100 rows in the range.

This is correct behavior, but it is often not what you want in a filtered report.

Use SUBTOTAL for Filtered Data

SUBTOTAL can calculate only visible filtered rows. Use =SUBTOTAL(9, B2:B100) for a sum that ignores rows hidden by filters.

If you also want to ignore manually hidden rows, use function number 109: =SUBTOTAL(109, B2:B100).

Use AGGREGATE to Ignore Errors

AGGREGATE is useful when the range contains error values. It can ignore hidden rows, nested totals, and errors depending on the option you choose.

For example, =AGGREGATE(9, 6, B2:B100) sums while ignoring errors. This is helpful when a dataset contains #N/A or #DIV/0! values.

SUMIF Mistakes

SUMIF can return wrong results when the criteria range and sum range are different sizes, when criteria contain hidden spaces, or when numbers are stored as text.

Use consistent ranges, clean spaces with TRIM, and convert text numbers before relying on SUMIF output.

Prevent Total Errors

Before calculating totals, check number format, remove extra spaces, convert imported numbers, and decide whether hidden rows should be included. If filters are part of the workflow, use SUBTOTAL instead of SUM.

Practical Rule

Use SUM for complete ranges. Use SUBTOTAL for filtered reports. Use AGGREGATE when errors or hidden rows need to be ignored. The right formula depends on what should be counted, not just what is visible on screen.

Automate Repetitive Excel Sum Work with inline AI

Once you know how to sum a column in Excel, the next step is reducing repeated total checks across reports. Wouldn't it be easier to find why totals do not match without manually checking every hidden row or text-formatted number?

inline AI is a local agent that works directly inside Excel. Ask in natural language, such as check why this column total is wrong, and it can read and edit your Excel file in real time.

Because it runs locally on your PC without uploading your data to the cloud, it can handle sensitive spreadsheets more safely. Download it today and experience the future of Excel work yourself.