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 outofthebox way to do it then (I can't see it, at least), because there is no way to find the nth element in Excel; but you should take a look at this: ozgrid.com/Excel/findnth.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.