Sum upwards by a number of rows

[Peter1973]

I am trying to work out how to look in a cell and then look up to the top of the sheet in than column how many cells have the same value and sum the corresponding cells next to them data example below

	A	B	C
1	WEST	3
2	EAST	2
3	WEST	4
4	WEST	2
5	WEST	1
6	EAST	5
7	WEST	TOTAL	10

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

The answer would be 10 in this case as all the cells in A that = A7 the sum of the cells in B=10.
I can do this with a sumif but the location of the cells moves and there is more than 1 sum in the data but i need to to look upwards in all cells above and a sumif gives me a circular reference.

Any help appreciated

 

[wideboydixon]

You could use OFFSET but it's volatile:

Book1
	A	B	C
1	WEST	3
2	EAST	2
3	WEST	4
4	WEST	2
5	WEST	1
6	EAST	5
7	WEST	TOTAL	10
Sheet1

Cell Formulas
Range	Formula
C7	=SUMIF(OFFSET($A$1,0,0,ROW()-1),$A7,OFFSET($B$1,0,0,ROW()-1,1))

WBD

 

[steve the fish]

Heres another couple of ways:

=SUMIFS($B$1:INDEX(B:B,ROWS($A$1:A7)-1,0),$A$1:INDEX(A:A,ROWS($A$1:A7)-1,0),A7)
=SUMIFS(INDIRECT("$B$1:B"&ROWS($A$1:A7)-1),INDIRECT("A1:A"&ROWS($A$1:A7)-1),A7)

 

[Peter1973]

Amazing, thanks for the help