SumIf vertically above cell equals variable value

[smiha946]

Hi,

Is there a way to apply a sumif formula to sum values in a column where the criteria is within the same column:
i.e, per the attached image, to sum the values in yellow. I have manually added in this instance.

Essentially want formula to say, if a cell in column D = my column C value ("New This Month), then sum the value below the match.

Maybe an offset formula within a sum if?

For cell D20, i tried: =sumif(D:D,$c20,offset(D:D,-1,0)) - but this returned an error.

Any ideas apprecaited!

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this

23 01 19.xlsm
	C	D
20	a	65000
21	b	10000
22	c	0
23	d	2000
24
25
26
27
28		a
29		10000
30
31
32		b
33		10000
34
35
36		a
37		40000
38
39
40		a
41		15000
42
43
44		d
45		2000
SUMIF

Cell Formulas
Range	Formula
D20:D23	D20	=SUMIF(D$28:D$44,C20,D$29:D$45)

 

[smiha946]

Welcome to the MrExcel board!

For the future: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this

23 01 19.xlsm
	C	D
20	a	65000
21	b	10000
22	c	0
23	d	2000
24
25
26
27
28		a
29		10000
30
31
32		b
33		10000
34
35
36		a
37		40000
38
39
40		a
41		15000
42
43
44		d
45		2000
SUMIF

Cell Formulas
Range	Formula
D20:D23	D20	=SUMIF(D$28:D$44,C20,D$29:D$45)

Thanks! This works perfectly.. much simpler solution

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.