Sum(if) problem

[sa02000]

I am using the following formula with ctrl+shift+enter to calculate the total number of cells that match month and date in certain cell (L8) here. I keep getting a zero. I tried this same formula to compare just one cell (so instead of putting $D$2:$D$1376 I put in only D17 and I get a 1 which means my date formats are ok... I checked few other cells too) but when I use it with range it doesn't work.

Any insights why its not working. Any other better way of doing this?

=SUM(IF(AND(MONTH($D$2:$D$1376)=MONTH(L8),YEAR($D$2:$D$1376)=YEAR(L8)),1,0))

Thank you in advance,
Jay

[daddylonglegs]

Hello Jay,

You can't use AND in array formulas like this because it returns a single result rather than an array. I'd suggest using SUMPRODUCT, thus avoiding CSE. Try

=SUMPRODUCT(--(MONTH($D$2:$D$1376)=MONTH(L8)),--(YEAR($D$2:$D$1376)=YEAR(L8)))

or even

=SUMPRODUCT(--(TEXT($D$2:$D$1376,"mmmyy")=TEXT(L8,"mmmyy")))

[sa02000]

I learned something new today. I am going home
Thank you very much.

Jay