Sum Functions by using date criterias

[Gokhan_ciftcioglu]

Hi guys,

In Column A there are lots of dates from different years, and in column B there are numbers that I have to sum. the problem is; I need to sum the numbers according to months and years. Are there any way to do that by using array formulas. I don't want to use the SUMIF function by seperating the months and years as a number into other columns. The formula I am trying to use is below but it doesn't work.

F2 cell : 01.01.2017 for the below formula

SUM(IF(AND(YEAR($A$2:$A$800)=YEAR(F$2);MONTH($A$2:$A$800)=MONTH(F$2));$B$2:$B$800))

Thanks in advance

 

[Fluff]

Try
=SUMPRODUCT((YEAR(A2:A800)=YEAR(F2))*(MONTH(A2:A800)=MONTH(F2)),(B2:B800))

 

[Gokhan_ciftcioglu]

thanks a lot, it works quite good

 

[Fluff]

You're welcome

 

[Aladin Akyurek]

AND does not work that way. Try: Control+shift+enter, not just enter...

=SUM(IF(YEAR($A$2:$A$800)=YEAR(F$2);IF(MONTH($A$2:$A$800)=MONTH(F$2));$B$2:$B$800)))

or

=SUM(IF($A$2:$A$800-DAY($A$2:$A$800)+1=F$2;$B$2:$B$800))

where F$2 is a first day date like 01.01.2017. The SUMPRODUCT version would be:

=SUMPRODUCT(($A$2:$A$800-DAY($A$2:$A$800)+1=F$2)+0;$B$2:$B$800)

and SUMIFS version:

=SUMIFS($B$2:$B$800;$A$2:$A$800;">="&F$2;$A$2:$A$800,"<="&EOMONTH(F$2;0))

 

[Gokhan_ciftcioglu]

Thank you my friend, your solutions are also very helpful.

 

[Aladin Akyurek]

Thank you my friend, your solutions are also very helpful.

You are welcome.