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.
In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability,...
In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference. Full column references Excel supports " full column " like this: = SUM ( A:A ) // sum all of column A...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
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.