How to Sum by month?

[khardu]

Hell dear Excel lovers.

I've my sheet setup with dates going from January 1 to December 31. It has two column namely Date and Debit now I want to sum the debit column by month. any help would be appreciated.

 

[Aladin Akyurek]

Hell dear Excel lovers.

I've my sheet setup with dates going from January 1 to December 31. It has two column namely Date and Debit now I want to sum the debit column by month. any help would be appreciated.

Some options...

1) Create a pivot table that groups dates by month and by year.

2) Invoke formulas...

Let A2:A400 house dates, B2:B400 the corresponding debits.

Let E2 house a month/year of interest like 1-Jan-12 and so on downwards.

F2, copy down:

Either... (all versions)

=SUMPRODUCT(($A$2:$A$400-DAY($A$2:$A$400)+1=$E2)+0,$B$2:$B$400)

Or... (2007 or later)

=SUMIFS($B$2:$B$400,$A$2:$A$400,">="&$E2,$A$2:$A$400,"<="&EOMONTH($E2,0))

 

[khardu]

Thank you Aladin Akyurek for your reply. actually it seems complicated to me, I am a new to Excel, could you please amend the following formula to my need:

=SUM(IF(MONTH(Date)=7,Debit,0))

this will calculate the sum for the month of July. what I want is to increase the number 7 automatically when I drag the formula down to December (i.e. 12) then it should come down to 1, and finally it should start to increase to June. My spreadsheet date begins from July and end at June. I have applied the Name Range to my data as:

Date Column = Date
Debit Column = Debit

Thanking you in advance.

 

[Aladin Akyurek]

Thank you Aladin Akyurek for your reply. actually it seems complicated to me, I am a new to Excel, could you please amend the following formula to my need:

=SUM(IF(MONTH(Date)=7,Debit,0))

this will calculate the sum for the month of July. what I want is to increase the number 7 automatically when I drag the formula down to December (i.e. 12) then it should come down to 1, and finally it should start to increase to June. My spreadsheet date begins from July and end at June. I have applied the Name Range to my data as:

Date Column = Date
Debit Column = Debit

Thanking you in advance.

X1: 1-Jul-12

X2, just enter and copy down as far as needed:

=EDATE($X$1,ROWS($X$2:X2)-1)

Y2, control+shift+enter, not just enter, and copy down:

=SUM(IF(MONTH(Date)=MONTH($X2),IF(YEAR(Date)=YEAR($X2),Debit,0)))

 

[khardu]

Thank you, this solved the problem.

Couldn't we just get rid of the reference cell (X1). In other words, is there a way to embed the Month in the formula so that when we drag the formula down it should change to the next month, i.e. July -> August -> September -> October -> November -> December -> January -> February -> up to June.

Once again I appreciate your response.

 

[Aladin Akyurek]

Thank you, this solved the problem.

You are welcome.

Couldn't we just get rid of the reference cell (X1). In other words, is there a way to embed the Month in the formula so that when we drag the formula down it should change to the next month, i.e. July -> August -> September -> October -> November -> December -> January -> February -> up to June.

Once again I appreciate your response.

Manual set up:

X2: 1-Jul-12
X3: 1-Aug-12

Select X2:X3 and copy down as far as needed.

 

[khardu]

Would you also please correct the following formula for me:

=SUMPRODUCT(MONTH(Date)=7,Debit)

this is for the month of July only.

I entered it with ctrl+enter and ctrl+shift+enter, it do not work in either case.

 

[Aladin Akyurek]

Would you also please correct the following formula for me:

=SUMPRODUCT(MONTH(Date)=7,Debit)

this is for the month of July only.

I entered it with ctrl+enter and ctrl+shift+enter, it do not work in either case.

Y2, just enter and copy down:

=SUMPRODUCT((Date-DAY(Date)+1=$X2)+0,Debit)

See the previous post for X2.