Calculations based on consecutive rows

[GLIBBY]

Hello all. Im wondering how I can do calculations on a column of cells, based on reference column having consecutive values. Say I have the following

A|B
-----
5|1
5|1
5|1
6|1
3|1
2|1
5|1
5|1
5|1

I want to have one cell (say C1) contain the sum of the first block of values in column B, that corresponds with the first block of consecutive 5s in column A. Then I'd have another cell (C2) contain the sum of the values in column B that corresponded with the second block of consecutive 5s, and so on for the entire spreadsheet.

Basically as soon as there is a break from a certain value (5 in this case) in a column I want a new calculation to be performed, for a different cell, on the next set of data that corresponds with consecutive values.

Does that make any sense? Thanks!

 

[T. Valko]

Hello all. Im wondering how I can do calculations on a column of cells, based on reference column having consecutive values. Say I have the following

A|B
-----
5|1
5|1
5|1
6|1
3|1
2|1
5|1
5|1
5|1

I want to have one cell (say C1) contain the sum of the first block of values in column B, that corresponds with the first block of consecutive 5s in column A. Then I'd have another cell (C2) contain the sum of the values in column B that corresponded with the second block of consecutive 5s, and so on for the entire spreadsheet.

Basically as soon as there is a break from a certain value (5 in this case) in a column I want a new calculation to be performed, for a different cell, on the next set of data that corresponds with consecutive values.

Does that make any sense? Thanks!

Is this what you want:

Book1
	A	B	C
2	5	1	_
3	5	1	_
4	5	1	3
5	6	1	_
6	3	1	_
7	2	1	_
8	5	1	_
9	5	1	_
10	5	1	_
11	5	1	4
12	1	1	_
13	5	1	1
14	2	1	_
15	5	1	_
16	5	1	2
17	3	1	_
18	5	1	_
19	5	1	_
20	5	1	_
21	5	1	4
Sheet1

 

[GLIBBY]

Yes exactly. The one thing I failed to mention it my initial post is that I wont always know where the consecutive blocks are, since they depend on another function. However, they will always be in the same column.

 

[T. Valko]

Yes exactly. The one thing I failed to mention it my initial post is that I wont always know where the consecutive blocks are, since they depend on another function. However, they will always be in the same column.

OK, based on the sample I posted in the other reply...

Enter this formula in C2:

=IF(AND(A2=5,A3=5),"",IF(A2=5,B2,""))

Enter this formula in C3 and copy down to the end of data:

=IF(AND(A3=5,A4<>5),SUMIF(A$2:A3,5,B$2:B3)-SUM(C$2:C2),"")

 

[GLIBBY]

Im realizing that what I need to implement is harder, and has a different approach from what I was describing earlier.

Its the same set-up with random blocks of consecutive data, but I want the result cell to be a subtraction between the first and last cell of a block. so if I have:

A|B
----
4|1
5|10
5|5
5|5
5|1
3|1

I'd expect the result of 9. Im having trouble wrapping my brain around "finding" the first and last consecutive entries. Thanks again.