When this formula is copied down the column, it correctly reports a running total on each row.
How this formula works
This formula uses what is called a "mixed reference" to create an "expanding range". A mixed reference is a reference that includes both absolute and relative parts.
In this case, the SUM formula refers to the range C6:C6. However, the first reference to C6 (on the left of the colon) is absolute and is entered $C$6. This "locks" the reference so that it won't change when copied.
On the right of the colon, the reference is relative, and appears as C6. This reference will change as the the formula is copied down the column.
The result is a range that expands by one row each time it is copied down:
=SUM($C$6:C6)// formula in D6=SUM($C$6:C7)// formula in D7=SUM($C$6:C8)// formula in D8
To create a running count of certain values that appear in range of cells, you can use the COUNTIF with a " mixed reference " to create a running total. In the example shown, the formula in C5 is: = IF ( B5 = "blue" , COUNTIF ( $B$5:B5 ,...
The Excel SUM function returns the sum of values supplied as multiple arguments. SUM can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
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.