Sum or Count until?

Question

I'm trying to make my Compliance Worksheet more efficient. I have a list of controls in sections (and sub-sections), and I use a value as a placeholder to count the number of controls per section (or sub-section), as well as exceptions per section. I use the value "1" if there is a valid control, and sum up these values per section or sub-section.

I often have to add rows to the bottom of a section, and this throws my sum-formula off, requiring manual updating to these formulas.

I would LIKE to utilize a formula to either "sum-until" or "count-until" the next section. I've attached an example.

Is there a way to Sum (or Count) until the next formula or non-"1"-value? Would it just be easier to put an "end" value at the bottom of each of these sections, and count until "end"? This wouldn't be an ideal way to perform such a function (as there will be a good number of unnecessary "ends" between sections), but if there's not a better way, perhaps I'll explore that avenue.

Answer 1

Use a Named Range for each section, then when you add the row, just add it to your named range.

So you could name a range 'Section1' to be C3:C9, then when you add a row, just right click on the row number 9, and 'Insert', then the new row will be in 'Section1'. So then you can just put the formula in as

=Sum(Section1)

Answer 2

Use a dynamic named range.

Excellent resource here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Basically you reference the named range in your sum formula, but since the range is dynamic it adjusts automatically.

(Kind of a combination of Jerry and Lance's answers)