SUMPRODUCT with YEAR, MONTH functions

[Attila Fust]

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]

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)

 

[Harlan Grove]

...
...

=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.

 

[Attila Fust]

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(DATES1)=YEAR

(A20)),SALES)

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

Attila

 

[Bob Phillips]

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]

Your help is much appreciated.

Regards,

Attila Fust