Thanks Bob. I've looked at a few websites to try to learn more, and this has
to be the best-organized one I've seen so far.

"Bob Phillips" wrote:

also, take a look at http://xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

-------

Bob Phillips
"Peo Sjoblom" wrote in message
...
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.