2

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)

4

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.