Sum if offset cell equals particular value

[jbenfleming]

So lets say i have two columns. One with names (John and Jane) and one with values. What is a formula I could input in B5 and B6 (John and Jane respectively) that would show the sum for each person. For instance the formula in B5 would return 2 and B6 would return -2. The list I'm using will have rows added regularly. I know that I could use VBA but I would rather not.

[TABLE="width: 200"]
<tbody>[TR]
[TD]JOHN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]JANE[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]JANE[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TOTAL JOHN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL JANE[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try the sumif() function

 

[jbenfleming]

I've tried but I haven't figured out a way to make it work for this purpose.

 

[AlanY]

ok, try this

Excel 2013/2016
	A	B
1	JOHN	1
2	JANE	-1
3	JANE	-1
4	JOHN	1
5	JANE	1
6	JOHN	1
7	JANE	5
8	JOHN	3
9	JANE	2
10	JOHN	5
11	JANE	3
12	JOHN	3
13	JOHN	2
14	JANE	1
15	JOHN	3
16	TOTAL
17	JOHN	16
18	JANE	10
Sheet3

Cell Formulas
Range	Formula
B17	=SUMIF(INDIRECT("$A$1:$A$"&MATCH("TOTAL",A:A,0)-2),A17,INDIRECT("$B$1:$B$"&MATCH("TOTAL",A:A,0)-2))

 

[jbenfleming]

Exactly what I needed. Thank you!

 

[AlanY]

you're welcome

 

[jbenfleming]

One question, shouldn't the match functions have "-1" at the end rather than "-2"? Otherwise you are omitting the last row of data (John 3, in this case)

 

[AlanY]

yes, you're right

 

[npvrader]

I don't see why you need the complex formula, a simple sumif(a1:a15,a17,b1:b15) should work?

 

[jbenfleming]

Lol, yup. That is much better. Thank you. I'm used to doing everything with VBA so I have an admittedly poor skill set with in sheet formulas. Getting better though.