title: How to convert address match formulas into dynamic ranges?
body: <p>I'm trying to develop a formula that sums cells in a column based on a range set dynamically by the addresses of two cells with specific text that change address depending on how many rows are between the two cells.</p>
  
  <p>Example: sheet <code>red_fruit</code></p>
  
  <pre><code>    |        A                        |        B   |      C
   1  |       Fruit store               |            |      
   2  |                                 |   apples   |      5
   3  |                                 |   apples   |      5
   4  |                                 |   oranges  |      1
   5  |  Fruit store branches (text)    |            |
  </code></pre>
  
  <p>In a second sheet called "summary", using the data from sheet "red_fruit" I want to add the amount of apples in column C based on a range set from row "fruit store" to row "fruit store branches" and ignore the amount of oranges.</p>
  
  <p>However, I do not want to use absolute cell addresses (A1, B2, etc) because I know that the columns between "fruit store" and "fruit store branches" will change in future sheets.</p>
  
  <p>I also need to constrain the C values to the depth of "fruit store branches" because I know that in the future there will be more data after that row.</p>
  
  <p>In the past I have been able to do this with the following formula:</p>
  
  <pre><code>=SUMIF( 'red_fruit'!$B$1:B, "apples*", 'red_fruit'!$C$1:C )
  </code></pre>
  
  <p>But that only works for absolute values for the B column and it does not have dynamic limits neither for the B column nor for the C column.</p>
  
  <p>So now I need to figure out a way to replace the B values with a dynamic formula that adjusts to automatically if I add more rows between "fruit store" and "fruit store branches".</p>
  
  <p>I was able to get the address for column B dynamically using the following formulas:</p>
  
  <p>For the "fruit store" row:</p>
  
  <pre><code>=ADDRESS((MATCH("fruit store",A1:A,1)),2,4)
  </code></pre>
  
  <p>The result is B1</p>
  
  <p>For the "fruit store branches" row:</p>
  
  <pre><code>=ADDRESS((MATCH("fruit store branches",A1:A,1)),2,4)
  </code></pre>
  
  <p>The result iss B5</p>
  
  <p>Here's where I got stuck. I have been unable to mix the formulas to create a dynamic range. The best I have been able to come up with this the following formula, but it returns an error:</p>
  
  <pre><code>=SUMIF( indirect"&amp;(ADDRESS((MATCH("fruit store",A1:A,1)),2,4):"&amp;(ADDRESS((MATCH("fruit store branches",A1:A,1)),2,4) , "apples*", c1:c ))
  </code></pre>
  
  <p>What I'm looking for would look like this:</p>
  
  <pre><code>=SUMIF( 'red_fruit'!dynamic_cell_address_formula1:'red_fruit'!dynamic_cell_address_formula2 , "apples", 'red_fruit'!$C$1:C )
  </code></pre>