Exceljet

Quick, clean, and to the point

Sum entire row

Excel formula: Sum entire row
Generic formula 
=SUM(1:1)
Summary 

To sum an entire row without providing a specific range, you can use the SUM function with a full row reference. In the example shown, the formula in C11, copied down, is:

=SUM(5:5)

The result is the sum of all numbers in row 5. As new data is added to the table, the formula will continue to return a correct total.

Explanation 

In this example, the goal is to return the sum for an entire row in an Excel worksheet. One way to do this is to use a full row reference.

Full row references

Excel supports full row references like this:

=SUM(1:1) // sum all of row 1
=SUM(3:3) // sum all of row 2
=SUM(4:5) // sum all of rows 4 and 5

You can see how this works yourself by typing 1:1 or 3:3 into the name box (left of the formula bar) and hitting return. You will see Excel select the entire row.

Example

To solve the problem in the example worksheet, we can use a full row reference to row 5 with the SUM function like this:

=SUM(5:5)

The result is the sum of all numeric values in row 5. As the formula is copied down, we get a sum for row 6 and row 7 as well:

=SUM(5:5) // sum red
=SUM(6:6) // sum blue
=SUM(7:7) // sum green

As new entries for "Red" are added to the table in rows 5, 6, and 7, the formula will automatically include these new amounts.

Advantages and risks

The main advantage to full row references is simplicity. Simple and very compact, a full row reference will automatically include all data in a row, even when data is added or removed. However, full row references come with certain risks. One risk is that you may accidentally include extra data in a calculation. For example, if you use =SUM(5:5) to sum numbers in row 5, you are targeting over 16,000 cells to the right. If row 5 includes extra dates somewhere far to the right, the numeric values of these dates will be included, and SUM will return an incorrect result. 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.