stuck on a sumif formula

[bigdan]

In col A I have a bunch of dates. From Jan 1 2016 to Dec 31 2017. In the format 1/20/2017 etc.
In col B I have the expenses of that date.

I want to break this down by month, using formulas not pivot tables. So in col C I've written Dec-17, Nov-17, etc.

What formula would I type in col D, to get the appropriate values? I've tried a sumif but I cant figure out how to make this work.

Thanks!


[TABLE="width: 500"]
<tbody>[TR]
[TD]12/1/2017[/TD]
[TD]$50[/TD]
[TD]Dec-17[/TD]
[TD]what formula goes here?[/TD]
[/TR]
[TR]
[TD]11/20/2017[/TD]
[TD]$60[/TD]
[TD]Nov-17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2016[/TD]
[TD]$75[/TD]
[TD]Oct-17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/20/2016[/TD]
[TD]0[/TD]
[TD]Sep-17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/31/2016[/TD]
[TD]0[/TD]
[TD]Aug-17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JumpingCrab]

In D1 you can place the following formula:
=SUMIFS($C$2:$C$6,$B$2:$B$6,">="&D2)

In D2 you place:
=SUMIFS($C$2:$C$6,$B$2:$B$6,">="&D3,$B$2:$B$6,"<"&D2)

You can the drag the D2 formula down. The & in the criteria allows you to make it dependent on the content of a cell. It's a pretty nice feature.

Hope this helps.

 

[bigdan]

I'm not at a computer right now but I notice in D2 you seem to be making a self-referential error (not sure the proper term, but D2 seems to refer to itself). I assume that's a typo?

I'd like to try your method since you're saying it's a pretty nice feature, so do please clarify.

Is there also a way to write something like
sumif(a1:a5, 'same month as c1', b1:b5) ? Essentially just trying to figure out what goes in the middle parameter?

In D2 you place:
=SUMIFS($C$2:$C$6,$B$2:$B$6,">="&D3,$B$2:$B$6,"<"&D2)

You can the drag the D2 formula down. The & in the criteria allows you to make it dependent on the content of a cell. It's a pretty nice feature.

Hope this helps.

 

[AhoyNC]

Are the values in column C actual dates that you formatted as Dec - 17 or are they text?

 

[jtakw]

Hi,

First, the formatting in Column C is important here, cell format needs to be Custom, mmm-yy, and entered as any date of the month.

If you only care about the Month regardless of Year for the SUM:

Excel 2010
	A	B	C	D
1	12/1/2017	$50.00	Dec-17	$61.00
2	11/20/2017	$60.00	Nov-17	$60.00
3	3/1/2016	$75.00	Oct-17	$0.00
4	2/20/2016	$0.00	Sep-17	$0.00
5	1/31/2016	$0.00	Aug-17	$0.00
6	12/15/2016	$10.00	Dec-16	$61.00
7	12/10/2017	$1.00	Nov-16	$60.00
Sheet2

Cell Formulas
Range	Formula
D1	=SUMPRODUCT((MONTH(A$1:A$7)=MONTH(C1))*(B$1:B$7))

But if you also need the Year in the criteria:

Excel 2010
	A	B	C	D
1	12/1/2017	$50.00	Dec-17	$51.00
2	11/20/2017	$60.00	Nov-17	$60.00
3	3/1/2016	$75.00	Oct-17	$0.00
4	2/20/2016	$0.00	Sep-17	$0.00
5	1/31/2016	$0.00	Aug-17	$0.00
6	12/15/2016	$10.00	Dec-16	$10.00
7	12/10/2017	$1.00	Nov-16	$0.00
Sheet1

Cell Formulas
Range	Formula
D1	=SUMPRODUCT((MONTH(A$1:A$7)=MONTH(C1))*(YEAR(A$1:A$7)=YEAR(C1))*(B$1:B$7))

In either case, D1 formula copied down.

 

[Dave Patton]

You can use Sumif or Sumifs if your version of Excel includes Sumifs.
You can implement the formula a variety of ways.
Several examples are shown below.

Excel 2010
	A	B	C	D	E
1	Date	Amount		Sumifs	Sumif
2	1-Dec-17	50.00	1-Dec-17	50	50
3	20-Nov-17	60.00	1-Nov-17	60	60
4	15-Oct-17	75.00	1-Oct-17	125	125
5	20-Oct-17	20.00
6	31-Oct-17	30.00
7
8	Date	Amount	30-Sep-17	Sumifs	Sumif
9	1-Dec-17	50.00	31-Oct-17	125	125
10	20-Nov-17	60.00	30-Nov-17	60	60
11	5-Oct-17	75.00	31-Dec-17	50	50
12	20-Oct-17	20.00
13	31-Oct-17	30.00
2d

Cell Formulas
Range	Formula
D2	=SUMIFS($B$2:$B$6,$A$2:$A$6,">="&C2,$A$2:$A$6,"<="&EOMONTH(C2,0))
D9	=SUMIFS($B$9:$B$16,$A$9:$A$16,">"&C8,$A$9:$A$16,"<="&C9)
E2	=SUMIF($A$2:$A$6,">="&C2,$B$2:$B$6)-SUMIF($A$2:$A$6,">"&EOMONTH(C2,0),$B$2:$B$6)
E9	=SUMIF($A$9:$A$13,">"&C8,$B$9:$B$13)-SUMIF($A$9:$A$13,">"&C9,$B$9:$B$13)

 

[JumpingCrab]

I'm not at a computer right now but I notice in D2 you seem to be making a self-referential error (not sure the proper term, but D2 seems to refer to itself). I assume that's a typo?

I'd like to try your method since you're saying it's a pretty nice feature, so do please clarify.

Good catch. I had my formula in column E (not D). Sorry about that. I had copied your example data into cell B2, so everything is shifted one column over.
Just change the references back to the correct columns for your case. It should work.

 

[JumpingCrab]

I'm not at a computer right now but I notice in D2 you seem to be making a self-referential error (not sure the proper term, but D2 seems to refer to itself). I assume that's a typo?

I'd like to try your method since you're saying it's a pretty nice feature, so do please clarify.

Is there also a way to write something like
sumif(a1:a5, 'same month as c1', b1:b5) ? Essentially just trying to figure out what goes in the middle parameter?

So there is another way to do this using array formulas (not sure if you are familiar with those, but they can do some pretty amazing stuff)

You type the following formula in D1:
=SUM(IF(MONTH($A$1:$A$5)=MONTH(C1),$B$1:$B$5,0))
Then don't hit ENTER<enter>, but <ctrl><shift><enter> simultaneously hit CTRL-SHIFT-ENTER.

The formula now looks like this (added curly braces around it):
{=SUM(IF(MONTH($A$1:$A$5)=MONTH(C1),$B$1:$B$5,0))}

You can now just drag it down to the other cells in D

It works by adding values in column B if the month in column A is the same as the month in the appropriate cell in column C. The array formula does this for all cells in the range you specify automatically. I am always surprised to see how powerful this can be.
Note: If you edit the formula you have to hit CTRL-SHIFT-ENTER again to keep it an array formula.

Cheers,
JL</enter></shift></ctrl></enter>

 

[bigdan]

Thanks a lot guys. I cant actually understand these formulas, but they do seem to work so I guess that's good enough

 

[jtakw]

You're welcome.