SUMPRODUCT help

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

Thanks for your help. Out of curiousity, do you know why in my Excel I have
to put the array to be summed (e.g. $Z$1:$Z$1000) as the first argument when
everyone else seems to put it as the last argument? Is there something in
the settings I can change to be more in line with the norm?

 

[Guest]

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.

 

[Ken Wright]

=SUMIF(A:A,"<="&TODAY(),B:B)-SUMIF(A:A,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())
,1),B:B)

 

[Harlan Grove]

Ken Wright wrote...

=SUMIF(A:A,"<="&TODAY(),B:B)-SUMIF(A:A,"<"&DATE(YEAR(TODAY()),
MONTH(TODAY()),1),B:B)

It's a lot easier to calculate the firzt day of the current month as

TODAY()-DAY(TODAY())+1

Using this, a shorter alternative,
=SUMIF(A:A,">"&(TODAY()-DAY(TODAY())),B:B)-SUMIF(A:A,">"&TODAY(),B:B)

 

[Ken Wright]

Cheers Harlan - hadn't even considered using DAY() to try and get there.
However, now you've gotten me there, in theory if it was a real MTD figure
then I guess it should be covered by just the first part of that

=SUMIF(A:A,">"&(TODAY()-DAY(TODAY())),B:B)

Now if he'd only not thrown in that bit about "or the date I'm reporting
on" <g>

 

[Guest]

Thanks for your help. The using reporting date rather than today's date
actually wasn't a problem with the formula you all provided. Thanks again.