1. You can't use the whole column like in B:B in any array formula, if you
want to sum what's in Z where B is year and month form A7? One way would be

=SUMPRODUCT(--(YEAR($B$1:$B$1000)=YEAR($A$7)),--(MONTH($B$1:$B$1000)=MONTH($A$7)),$Z$1:$Z$1000)

if you want to sum everything in Z where B is earlier than A7 use

=SUMPRODUCT(--($B$1:$B$1000<$A$7),$Z$1:$Z$1000)

both entered normally


Regards,

Peo Sjoblom

"benb" wrote:

I'm still learning all the capabilities of the SUMPRODUCT function and need
help adapting it to my current purpose. I have a column of dates, and I am
trying to obtain a MTD total from corresponding column of values. I figured
there is probably a way of nesting a MONTH function within the SUMPRODUCT to
do this. Using a date range would be a round about way of accomplishing
this, but I'd rather be able to define the month by referring to a single
cell with today's date (or the date I am reporting on). My failed attempt
looked something like this:

=SUMPRODUCT((Z:Z)*(MONTH($B:$B)=MONTH($A$7))*(YEAR ($B:$B)=YEAR($A$7)))

My guess is that I need to be using an array function, but Ctrl+Shift+Enter
is no help.

Thanks in advance. I always try to return the favor, but it seems like no
matter what I get more help from you all than I am able to offer myself.