summing values in one column that are referenced to values in another

[Colin]

Hello
hello my question involves to columns of a large data
set. For example"

A B
156 12
157 3
158 9
159 12
160 14
161 0.3
187 21
188 0.9
189 2
190 9.6

Column A has 30 000 plus rows and the jump in numbers
similar to that between 161 and 187 in my example occurs
every 26 rows.

I would like a way to sum column B for numbers 187 to 190
and then be able to copy this formula down for the whole
page. The answer in my C column corresponding to A190
for this example would be 33.3.

Thanks for trying to help

 

[Guest]

Close. I wnat to go back to the jump and sum up to the
current postion whatever that may be. I guess my main
problem is to go back only to the jump. I realize that
if i just fill in the numbers between the jumps I can
just sum the last 26 rows. This would not be possible to
do manually but if you know of function that would do it
that could be just as useful because the new column 'A'
numbers would have no values associated with them to sum.

Thanks Colin

-----Original Message-----
So am I right in saying you want to look for the "jump"

in column A, and then sum the next four numbers?

Enter in C2:

=If(A2=A1+26, sum(B2:B5),"")

This will then produce a total on every line where there

has been a jump of 26 in the value of the cell in column
A over that of the previous row, calculated by adding up
the total in column B on that row along with the values
in column B in the next three rows.