How to sum multiple columns in excel

Answer

You can sum multiple columns in Excel depending on what you mean by “sum multiple columns.” Below are the main cases and how to do each.

➤ Sum all values in several columns (treat as one big range)

To sum everything in several columns (for example, columns A through D), do this:

1. Select an empty cell where you want the total.

2. Enter a formula like:

   =SUM(A:A, B:B, C:C, D:D)

   or if you only want part of each column (e.g. rows 2 through 100):

   =SUM(A2:D100)

3. Press Enter — Excel returns the total sum of all numeric values in those columns.

You can do the same using the built-in AutoSum feature: place the cursor where you want the result, click AutoSum (Σ) from the Home tab (or Formulas tab), then adjust the range if needed, then press Enter.

➤ Sum across columns for each row (per-row totals)

To compute a row-by-row sum across multiple columns — for example, sum columns B, C, and D for each row — follow these steps:

1. Insert a new column to the right or left of your data (optional, but helps organization).

2. In the first row of that column (e.g. row 2), enter a formula such as:

   =SUM(B2:D2)

3. Press Enter.

4. Use the fill handle (the small square at the bottom-right of the cell) to drag the formula down through all rows.

As a result, each row will show the total of columns B, C, D for that row. 

➤ Sum multiple columns with conditions (filtering rows and summing multiple columns per those rows)

When you want to sum only certain rows based on criteria — and sum across multiple columns — you have choices. The usual function for conditional sums, SUMIFS, does not support summing multiple columns in one go.

Here are two workarounds:

• Using SUMPRODUCT

If you need the sum over several columns but only for rows meeting certain criteria, you can use SUMPRODUCT like this:

=SUMPRODUCT( (CriterionRange = Criterion) * (Col1Range + Col2Range + Col3Range) )

Example: Suppose column A has categories, and columns B, C, D hold numeric values. To sum B, C, D for rows where A = "North":

=SUMPRODUCT( (A2:A100="North") * (B2:B100 + C2:C100 + D2:D100) )

This produces the combined sum of B, C, D for all rows satisfying the criterion.

• Using a helper column + SUMIFS

You can first compute per-row totals in a helper column, then apply SUMIFS on that helper column. Steps:

1. In a new column (e.g. E), in E2 put:

   =B2 + C2 + D2

   then fill down.

2. Use SUMIFS to total E only for rows that meet your criteria, e.g.:

   =SUMIFS(E2:E100, A2:A100, "North")

This method avoids complex array formulas and is easier to audit if you have many columns or complex criteria.

Why can SUMIFS alone not sum multiple columns in one go

SUMIFS is designed to take a single “sum_range” — a single contiguous range of cells. It does not accept multiple, separate sum ranges across different columns to combine. Trying to give SUMIFS a multi-column sum_range will result in unexpected behavior (only first column gets summed) or errors. 

When should you choose which method?

• Use SUM(A: B, …) or AutoSum when you want to ignore structure and get the total of many columns.

• Use the row-by-row SUM function (SUM(B2:D2) when you need totals per row.

• Use the SUMPRODUCT method when you need to sum multiple columns but only for rows that meet certain criteria.

• Use helper-column + SUMIFS when you want clarity, easier audits, or many columns/criteria.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report