SUM IF DATE IN IN THAT MONTH

[superstarr]

hi
need a little hlp pls
have a range of dates, ie 3/11/2004
would like to have the sum of all the dates by month
tried the sumif, but cant figure out how to tell formula that 3/11/2004 is in march so add to the march total

thx much

JOn

 

[Aladin Akyurek]

Hi,

Sorry to reopen, what seems to be a closed topic, but i have hit a snag with this solution.

It has worked perfectly for all of the months, other than january, which is giving a total of all of the months combined. (i assume its, seeing the 1 as true)

Any ideas how to resolve this please?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($A$2:$A$6),IF($A$2:$A$6-DAY($A$2:$A$6)+1=$E2-DAY($E2)+1,$B$2:$B$6)))

 

[rhyse1977]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($A$2:$A$6),IF($A$2:$A$6-DAY($A$2:$A$6)+1=$E2-DAY($E2)+1,$B$2:$B$6)))

I adjusted the above to suit my spreadsheet but unfortunately, it didnt work.

i current have (using ctrl+shift+enter)

=SUM(IF(MONTH($K$2:$K$201)=1,$H$2:$H$201,0)) as i said, works for ever month apart from this (jan)

<tbody>
</tbody>

 

[Aladin Akyurek]

I adjusted the above to suit my spreadsheet but unfortunately, it didnt work.

i current have (using ctrl+shift+enter)

=SUM(IF(MONTH($K$2:$K$201)=1,$H$2:$H$201,0)) as i said, works for ever month apart from this (jan)

<tbody>
</tbody>

Are you not concerned about the year? Is it 2014, 2015, or what?

 

[rhyse1977]

Are you not concerned about the year? Is it 2014, 2015, or what?

no the year doesnt matter, as there will be a new sheet each year.

 

[Aladin Akyurek]

I adjusted the above to suit my spreadsheet but unfortunately, it didnt work.

i current have (using ctrl+shift+enter)

=SUM(IF(MONTH($K$2:$K$201)=1,$H$2:$H$201,0)) as i said, works for ever month apart from this (jan)

<tbody>
</tbody>

Are you not concerned about the year? Is it 2014, 2015, or what?

no the year doesnt matter, as there will be a new sheet each year.

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($K$2:$K$201),IF(MONTH($K$2:$K$201)=1,$H$2:$H$20)))

 

[rhyse1977]

thanks for the help, that worked perfectly

 

[Aladin Akyurek]

thanks for the help, that worked perfectly

You are welcome.

 

[brbell01]

This has been a helpful thread. Thank you. An added level of complexity (another if statement) and it seems to break the array. Any ideas whats wrong with my statement? See screenshot below for some sample data.

{=IF($C$3:$C$2000="bella",SUM(IF(MONTH($A$3:$A$2000)=7,$B$3:$B$2000)),0)}

<colgroup><col width="87" style="width:65pt"> </colgroup><tbody>
<!--StartFragment-->
<!--EndFragment-->
</tbody>

<attachment></attachment>

 

[brbell01]

date	amount	employee
22-Jul-16	$28.81	bob
1-Jul-16	$151.20	bob
14-Jul-16	$8.00	vance
13-Jul-16	$21.90	vance
14-Jul-16	$302.00	bob
25-Jul-16	$15.00	bob
14-Jul-16	$9.00	bella
13-Jul-16	$8.80	bella
4-Jul-16	$30.40	bella
14-Jul-16	$5.98	vance
19-Jul-16	$60.00	vance
4-Jul-16	$24.90	vance

<!--StartFragment--> <colgroup><col width="87" span="2" style="width:65pt"> <col width="87" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[Aladin Akyurek]

@brbell01

1. If a year test is not needed: Control+shift+enter, not just enter...

=SUM(IF(MONTH($A$3:$A$2000)=7,IF($C$3:$C$2000="bella",$B$3:$B$2000)))

2. If you have the SUMIFS function on your system: Just enter...

=SUMIFS(B:B,C:C,"bella",A:A,">="&DATE(2016,7,1),A:A,"<="&DATE(2016,7,31))