Help with Array Formula

[dhstein]

I have a list of expenses. Column A has the date of the expense and column D has the amount. I want to create a table that sums the expense by month. In column CE I have a date, for example 12/1/2014. I created an array formula which gives me all expenses for the month - =SUM((MONTH($A$7:$A$2000)=MONTH(CE10))*$D$7:$D$2000). However, December will be listed for 2014 and also for 2015. So I want to modify the formula to also account for the year. I tried this, but it didn't work - =SUM(AND((MONTH($A$7:$A$2000)=MONTH(CE10)),YEAR($A$7:$A$2000)=YEAR(CE10))*$D$7:$D$2000) Can anyone help with this? Thanks.

[azumi]

perhaps :

=SUM((YEAR($A$7:$A$2000)=YEAR(CE10)*(MONTH($A$7:$A$2000)=MONTH(CE10))*$D$7:$D$2000))

and array entered

[Pete_UK]

Try this array* formula:

=SUM((TEXT($A$7:$A$2000,"yymm")=TEXT(CE10,"yymm"))*($D$7:$D$2000))

*Don't forget to use Ctrl-Shift-Enter to commit the formula.

Or you could try this normally-entered formula:

=SUMPRODUCT((TEXT($A$7:$A$2000,"yymm")=TEXT(CE10,"yymm"))*($D$7:$D$2000))

Hope this helps.

Pete

[dhstein]

Thanks Azumi - that worked !