How can I sum a range in Excel from the indicies of the start- and end-points?

Question

I have a spreadsheet that contains a list of data points over time, along with some time values that denote events. I want to sum all the data points between each of the event values.

My spreadsheet looks something like this:

The index of the cell closest to the event I can obtain by using =MATCH(K13, $B$12:$B$8889, 1) (for example), which returns the index (e.g. 301 as above). However, I don't know how to go from those indicies, which specify a cell, to the sum of a series.

The value I can swipe with something like =INDEX($G$12:$G$8889,M13), but I don't want just the one value, I want to sum everything between, say, 1 (aka B12) and 301 (B312).

Answer 1

Why do you need the indices? Use the SUMIFS function. I don't see a row 301 in your picture, but let's say you want to sum everything between the third (K14) and tenth (K20) event. The formula should be something like

=SUMIFS(G2:G8889, B2:B8889, ">" & K14; B2:B8889, "<" & K20) 

Some tweaking may be required, because I haven't tried it on your example. The reference for the function is linked above.

Answer 2

=SUM(INDIRECT(ADDRESS(1, 7) & ":" & 
              ADDRESS(MATCH(K13, $B$12:$B$8889, 1), 7))

but personally I'd use the SUMIFS function as shown in rumtscho's answer.