SUMPRODUCT to also MATCH date

[ddub25]

=SUMPRODUCT(--('Rec. Shared'!$M$12:$M$848=$A$47),'Rec. Shared'!$AE$12:$AE$848)

This formula looks at $A$47 for a text value ('Andy M' is displayed) and then looks in the range 'Rec. Shared'!$M$12:$M$848 to see if it can find a MATCH. If it does it then SUMS the corresponding rows in 'Rec. Shared'!$AE$12:$AE$848.

I'd like to adapt this formula further to look at the month/year displayed in A52, and find dates in 'Rec. Shared'!$AI$12:$AI$848 that fall within this month/year and only SUM these values as well as retaining the current formula conditions which must still be met.

The date format in A52 is mmm-yy (Sep-10)
The date format in 'Rec. Shared'!$AI$12:$AI$848 is '14/03/01' (Excel 2007, I believe).

Can anyone help?

Dan

 

[p45cal]

Try:
=SUMPRODUCT(('Rec. Shared'!$M$12:$M$848=$A$47)*(YEAR('Rec. Shared'!$AI$12:$AI$848)=YEAR($A$52))*(MONTH('Rec. Shared'!$AI$12:$AI$848)=MONTH($A$52)),'Rec. Shared'!$AE$12:$AE$848)

should work as long as dates are excel dates, regardless of how they're formatted.

 

[ddub25]

Thanks p45cal, that's great.

Dan