求和还是计数直到？
我正在努力使我的合规工作表更加高效。我有一个节（和子节）中的控件列表，我使用一个值作为占位符来计算每个节（或子节）的控件数量以及每个节的异常数。如果存在有效的控件，我将使用值“1”，并对每个部分或子部分的这些值进行求和。
我经常需要在某个部分的底部添加行，这会导致我的求和公式失效，需要手动更新这些公式。
我想使用一个公式来“求和直到”或“计数直到”下一部分。我附上了一个例子。
有没有办法求和（或计数）直到下一个公式或非“1”值？在每个部分的底部放置一个“结束”值并计数直到“结束”会更容易吗？这不是执行此类功能的理想方法（因为各部分之间会有大量不必要的“结束”），但如果没有更好的方法，也许我会探索该途径。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
为每个部分使用命名范围，然后当您添加行时，只需将其添加到命名范围即可。
因此，您可以将范围“Section1”命名为 C3:C9，然后当您添加行时，只需右键单击行号 9，然后“插入”，那么新行将位于“Section1”中。那么你可以将公式输入为
=Sum(Section1)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)使用动态命名范围。
这里有优秀的资源：http://www.ozgrid.com/Excel/DynamicRanges.htm
基本上，您在求和公式中引用命名范围，但由于该范围是动态的，因此它会自动调整。
（有点像杰瑞和兰斯的答案的结合）
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)
=总和(偏移量(c3;0;0;c2;1)
=sum(offset(c3;0;0;c2;1)
将此公式用于
SubTotal行。对于C$1000，请选择比您预计表格增长到的行更大的行。用于输入单元格
C2。复制并粘贴到所有其他
SubTotal行Use this formual for the
SubTotalrows. ForC$1000choose a row greater than what you expect your table to grow to.For entry in cell
C2.Copy and paste to all other
SubTotalrows