Sum by Current Month

[dgh71]

Hi,

Have simple table as follows:

Date Total
01/08/11 $100
15/08/11 $50
03/09/11 $25
11/09/11 $50

I need a formula (sumif?) that will allow me to sum all values that apply to the current month. For example in the able table the current month would be 9 and therefore the total would be $75

Thanx in advance
Dave

 

[Aladin Akyurek]

Hi,

Have simple table as follows:

Date Total
01/08/11 $100
15/08/11 $50
03/09/11 $25
11/09/11 $50

I need a formula (sumif?) that will allow me to sum all values that apply to the current month. For example in the able table the current month would be 9 and therefore the total would be $75

Thanx in advance
Dave

Try...

E2:

=TODAY()

=SUMIF($A$2:$A$5,">="&$E$2-DAY($E$2)+1,$B$2:$B$5)-SUMIF($A$2:$A$5,">"&EOMONTH($E$2,0),$B$2:$B$5)

If you are not on Excel 2007 or later, the Analysis Toolpak add-in must be active.

On Excel 2007 or later...

=SUMIFS($B$2:$B$5,$A$2:$A$5,">="&$E$2-DAY($E$2)+1,$A$2:$A$5,"<="&EOMONTH($E$2))

Generic...

Either:

=SUMPRODUCT($B$2:$B$5,($A$2:$A$5-DAY($A$2:$A5)+1=$E$2-DAY($E$2)+1)+0)

Or:

=SUMPRODUCT($B$2:$B$5,(TEXT($A$2:$A$5,"mmmyy")=TEXT($E$2,"mmmyy"))+0)