# Excel: Sum rows until a cell has a certain value

My spreadsheet has costs and subcosts. I'd like to total the costs automatically. Is there a formula that will sum the `item_cost` column for all rows beneath this one until the first row which is marked `rollup`. Something like `SUM(A13:A[first_row_with_rollup=Yes]-1)`

You can use the function `MATCH(lookup_value, lookup_array, match_type)` to find the first occurrence of a value in a column. Then you can use that value to make the range of the sum function. Something like:

``````=SUM(INDIRECT(CONCATENATE("B1:B";MATCH("asd";B:B;0)-1)))
``````

Explanation:

``````MATCH("asd";B:B;0) - 1 // finds the first occurrence of "asd" on B column, gets the cell above it
CONCATENATE("B1:B";\$) // get the interval b1 to bn, where n is the cell found
INDIRECT(\$) // makes reference to the cell specified by the string "b1:bn"
SUM(\$) // sums the interval
``````

Tested in Google Spreasheets, but should work on Excel according to the docs.

• Thanks - what if I don't want the first occurrence of "asd" but the next occurrence (that is, beneath this row)? – SRobert James Jun 25 '14 at 21:35
• I don't think there is an easy elegant out-of-the-box way to do it then (I can't see it, at least), because there is no way to find the n-th element in Excel; but you should take a look at this: ozgrid.com/Excel/find-nth.htm They present a couple workarounds, including adding a hidden column with the "asd"s replaced with "asd 1", "asd 2", etc., and also using a custom function. – Luan Nico Jun 26 '14 at 16:34

I know this thread is old, but for the benefit of others who may search for a solution to this issue, you can try the following:

Assuming column of numbers to be summed is in column A, say `A10:A20`.
Assuming "Rollup" is in column B at each point where sum up to.
Place then following formula in `C10`:

``````=SUM(OFFSET(A10,0,0,MATCH("Rollup",B10:\$B\$20,FALSE),1))
``````

Note that the last cell in your column of numbers has an absolute reference. You can copy this down into `C11:C20` to see it in action.