SUMPRODUCT with YEAR, MONTH functions

I am trying to sum data if a date range matches (year and
month only). Here is the example:

Table1
5/15/00 5/16/00 6/17/00 (NAME=DATES1)
5 5 5 (NAME=SALES)

Table2
5/1/00 6/1/00 7/1/00 (NAME=DATES2)
sum1 sum2 sum3

In Table2 I want to create a formula to sum the data by
month under the appropriate month (ie. in sum1, sum2 and
sum3). Using the named ranges above I created the
following formula:

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR(DATES1)
=YEAR(DATES2)),SALES)

This formula does not work but the results I would like is
to have in Table 2 are - $10 under 5/1/00, $5 under
6/1/00 and $0 under 7/1/00. Unfortunately my formula does
not work.

Am I on the right track or should I do something else?

Thanks in advance.

Attila Fust

Assuming Table 2 is in say A20:M20

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)=YEAR(A20)),SALES)

and then copy across

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Attila Fust" wrote in message
...
I am trying to sum data if a date range matches (year and
month only). Here is the example:

Table1
5/15/00 5/16/00 6/17/00 (NAME=DATES1)
5 5 5 (NAME=SALES)

Table2
5/1/00 6/1/00 7/1/00 (NAME=DATES2)
sum1 sum2 sum3

In Table2 I want to create a formula to sum the data by
month under the appropriate month (ie. in sum1, sum2 and
sum3). Using the named ranges above I created the
following formula:

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR(DATES1)
=YEAR(DATES2)),SALES)

This formula does not work but the results I would like is
to have in Table 2 are - $10 under 5/1/00, $5 under
6/1/00 and $0 under 7/1/00. Unfortunately my formula does
not work.

Am I on the right track or should I do something else?

Thanks in advance.

Attila Fust

"Bob Phillips" wrote...
...
=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)=YEAR(A20)),SALES)
...

Or

=SUMIF(DATES1,""&(A20-DAY(A20)),SALES)
-SUMIF(DATES1,""&(A20-DAY(A20)+31-DAY(A20-DAY(A20)+31)))

or

=SUMPRODUCT(--(TEXT(DATES1,"yyyymm")=TEXT(A20,"yyyymm")),SALES)

The first, though longer, is likely to be fastest.

--
To top-post is human, to bottom-post and snip is sublime.

Thanks, that worked. I am wondering what the "--"
represents in the formula? If I take it out it does not
work. Using the following returns 0:

=SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1)=YEAR
(A20)),SALES)

I thought the "--" was just to make the formula easier to
read. What exactly does it mean?

Attila

-----Original Message-----
Assuming Table 2 is in say A20:M20

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)
=YEAR(A20)),SALES)

and then copy across

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Attila Fust" wrote
in message
...
I am trying to sum data if a date range matches (year
and
month only). Here is the example:

Table1
5/15/00 5/16/00 6/17/00 (NAME=DATES1)
5 5 5 (NAME=SALES)

Table2
5/1/00 6/1/00 7/1/00 (NAME=DATES2)
sum1 sum2 sum3

In Table2 I want to create a formula to sum the data by
month under the appropriate month (ie. in sum1, sum2 and
sum3). Using the named ranges above I created the
following formula:

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR
(DATES1)
=YEAR(DATES2)),SALES)

This formula does not work but the results I would like
is
to have in Table 2 are - $10 under 5/1/00, $5 under
6/1/00 and $0 under 7/1/00. Unfortunately my formula
does
not work.

Am I on the right track or should I do something else?

Thanks in advance.

Attila Fust


.

The comparisons returns arrays of Boolean values, TRUE and FALSE. The -- is
used to coerce those Booleans to 1/0 integers, which can simply be summed.

To see, take a small test range, and select the part of the formula in the
formula bar that says MONTH(DATES1)=MONTH(A20). Press F9 and you will see an
array of TRUE/FALSE (make the test range small so that F9 can evaluate it in
the formula bar).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Attila Fust" wrote in message
...
Thanks, that worked. I am wondering what the "--"
represents in the formula? If I take it out it does not
work. Using the following returns 0:

=SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1)=YEAR
(A20)),SALES)

I thought the "--" was just to make the formula easier to
read. What exactly does it mean?

Attila

-----Original Message-----
Assuming Table 2 is in say A20:M20

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)
=YEAR(A20)),SALES)

and then copy across

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Attila Fust" wrote
in message
...
I am trying to sum data if a date range matches (year
and
month only). Here is the example:

Table1
5/15/00 5/16/00 6/17/00 (NAME=DATES1)
5 5 5 (NAME=SALES)

Table2
5/1/00 6/1/00 7/1/00 (NAME=DATES2)
sum1 sum2 sum3

In Table2 I want to create a formula to sum the data by
month under the appropriate month (ie. in sum1, sum2 and
sum3). Using the named ranges above I created the
following formula:

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR
(DATES1)
=YEAR(DATES2)),SALES)

This formula does not work but the results I would like
is
to have in Table 2 are - $10 under 5/1/00, $5 under
6/1/00 and $0 under 7/1/00. Unfortunately my formula
does
not work.

Am I on the right track or should I do something else?

Thanks in advance.

Attila Fust


.

Your help is much appreciated.

Regards,

Attila Fust
-----Original Message-----
The comparisons returns arrays of Boolean values, TRUE
and FALSE. The -- is
used to coerce those Booleans to 1/0 integers, which can
simply be summed.

To see, take a small test range, and select the part of
the formula in the
formula bar that says MONTH(DATES1)=MONTH(A20). Press F9
and you will see an
array of TRUE/FALSE (make the test range small so that F9
can evaluate it in
the formula bar).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Attila Fust" wrote
in message
...
Thanks, that worked. I am wondering what the "--"
represents in the formula? If I take it out it does not
work. Using the following returns 0:

=SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1)
=YEAR
(A20)),SALES)

I thought the "--" was just to make the formula easier
to
read. What exactly does it mean?

Attila

-----Original Message-----
Assuming Table 2 is in say A20:M20

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR
(DATES1)
=YEAR(A20)),SALES)

and then copy across

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Attila Fust"
wrote
in message
...
I am trying to sum data if a date range matches (year
and
month only). Here is the example:

Table1
5/15/00 5/16/00 6/17/00 (NAME=DATES1)
5 5 5 (NAME=SALES)

Table2
5/1/00 6/1/00 7/1/00 (NAME=DATES2)
sum1 sum2 sum3

In Table2 I want to create a formula to sum the data
by
month under the appropriate month (ie. in sum1, sum2
and
sum3). Using the named ranges above I created the
following formula:

=SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR
(DATES1)
=YEAR(DATES2)),SALES)

This formula does not work but the results I would
like
is
to have in Table 2 are - $10 under 5/1/00, $5 under
6/1/00 and $0 under 7/1/00. Unfortunately my formula
does
not work.

Am I on the right track or should I do something
else?

Thanks in advance.

Attila Fust


.



.