Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

enter image description here

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).

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
=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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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