SUMPRODUCT help
 

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.

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.

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.

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?

"Peo Sjoblom" wrote:

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.

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.

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"benb" wrote in message
...
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.

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)

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" wrote in message
oups.com...
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)

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.

"Ken Wright" wrote:

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" wrote in message
oups.com...
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)