title: Formula that incorporates "xlUp" or dynamic range?
body: <p>I have data that comes in "blocks", that I need to <code>SUM()</code>.  However, the block sizes aren't the same, so I find myself manually adjusting the formula to fit the ranges.</p>
  
  <p>Sample data:<br>
  <a href="https://i.stack.imgur.com/K6l5t.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K6l5t.jpg" alt="enter image description here"></a><br></p>
  
  <pre><code>Chicago         10
  Denver          20
  Anchorage       22
  
  Denver          122
  Gotham          23
  Sky City        22
  New Jack City   2
  
  Sacramento      92
  
  Memphis         1
  Miami           22
  Mordor          3
  </code></pre>
  
  <p><sub>(Note: <a href="https://meta.stackexchange.com/questions/320162">I can't paste data here</a>, prompts me for image.)</sub></p>
  
  <p>The idea is to have a formula in column C that sums the data above.  So <code>C3</code> (or <code>C4</code>, whatever works) would sum <code>B1:B3</code>.  <code>C8</code> would sum <code>B5:B8</code>, etc.  </p>
  
  <p>I can do this with a UDF, no problem.  </p>
  
  <p>However, I'm curious if there's a formula I can use.  I've started with <code>SUMIF()</code> but get stuck almost immediately, as unfortunately my formula skills break down when trying to do something that incorporates an <code>IF()</code> within <code>SUM()</code>.  (My thoughts are the formula would need <code>AGGREGATE()</code> and <code>IF()</code> within <code>SUM()</code> ...like, <code>=SUM(IF(AGGREGATE(A1...</code> and I'm stuck...)</p>