Sumproduct Problem

[Gos-C]

Hi,

On *sheet2 of Book1*, Column C has two-character codes; Column M has
dates; Column R has $ amounts; and Column X has dates.

On *Sheet1 of Book2*, I want the total from Column R for code AB where
the *month* in Column M is less than the *date* in Column X.

I try the following formula:

=SUMPRODUCT(--('[Book1.xls]Sheet2'!C4:C1550="AB"),--(MONTH(DATE(YEAR('[Book1.xls]Sheet2'!M4:M1550),MONTH('[Book1.xls]Sheet2'!M4:M1550),DAY('[Book1.xls]Sheet2'!M4:M1550)))<MONTH(DATE(YEAR('[Book1.xls]Sheet2'!X4:X1550),MONTH('[Book1.xls]Sheet2'!X4:X1550),DAY('[Book1.xls]Sheet2'!X4:X1550)))),'[Book1.xls]Sheet2'!R4:R1550)

but it giving #VALUE! error.

Any help?

Thank you,
Gos-C

 

[Don Guillett]

just a cursor look but if you have valid dates

=sumproduct((month(daterange)=2)

 

[Peo Sjoblom]

The are some ambiguities

What does

"the *month* in Column M is less than the *date* in Column X."?

mean?

=SUMPRODUCT(--([Book1]Sheet2!$C$4:$C$1550="AB"),--(MONTH([Book1]Sheet2!$M$4:$M$1550)<MONTH([Book1]Sheet2!$X$4:$X$1550)),[Book1]Sheet2!$R$4:$R$1550)

will work if you want to SUM R where C is AB and where month in M is less
than month in X

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Bernie Deitrick]

Your formula worked for me, though you could simplify it to:

=SUMPRODUCT(--([Book1.xls]Sheet2!C4:C1550="AB"),--(MONTH([Book1.xls]Sheet2!M4:M1550)<MONTH([Book1.xls]Sheet2!X4:X1550)),[Book1.xls]Sheet2!R4:R1550)

If that gives you an error, you might have invalid dates in column M or column X

HTH,
Bernie
MS Excel MVP

 

[Gos-C]

The date in Column M has to be earlier than (*but not in the same mont
and year as*) the date in Column X.

Gos-

 

[Gos-C]

If some cells in Column M are blank would that be the cause of the
#VALUE! error?

Gos-C

 

[Bernie Deitrick]

Gos-C,

The blanks would just be treated as zero.

Try this, which will make sure that column M is filled, and that the date is earlier (and not in the
same month/year):

=SUMPRODUCT([Book1.xls]Sheet2!C4:C1550="AB",[Book1.xls]Sheet2!M4:M1550<>"",DATE(YEAR([Book1.xls]Sheet2!M4:M1550),MONTH([Book1.xls]Sheet2!M4:M1550),1)<DATE(YEAR([Book1.xls]Sheet2!X4:X1550),MONTH([Book1.xls]Sheet2!X4:X1550),1),[Book1.xls]Sheet2!R4:R1550)

HTH,
Bernie
MS Excel MVP

 

[Gos-C]

I tried all the suggestions (and check the dates, which appear to be OK)
but I am still getting the same error.

Gos-C

 

[Bernie Deitrick]

Gos-C,

Try breaking it down by parts:

=SUMPRODUCT([Book1.xls]Sheet2!C4:C1550="AB",1)

=SUMPRODUCT([Book1.xls]Sheet2!M4:M1550<>"",1)

=SUMPRODUCT(DATE(YEAR([Book1.xls]Sheet2!M4:M1550),MONTH([Book1.xls]Sheet2!M4:M1550),1)<DATE(YEAR([Book1.xls]Sheet2!X4:X1550),MONTH([Book1.xls]Sheet2!X4:X1550),1))

=SUMPRODUCT([Book1.xls]Sheet2!R4:R1550),1)

Which part(s) throw an error?

HTH,
Bernie
MS Excel MVP

 

[Gos-C]

Hi Bernie,

When I entered the different part as you indicated, I get the #VALUE!
error. But when I use the double unary operator, i.e., =SUMPRODUCT(--(
), only the date part does not work.

Gos-C

 

[Bernie Deitrick]

Try changing

DATE(YEAR([Book1.xls]Sheet2!M4:M1550),MONTH([Book1.xls]Sheet2!M4:M1550),1)<DATE(YEAR([Book1.xls]Sheet2!X4:X1550),MONTH([Book1.xls]Sheet2!X4:X1550),1)

To

DATE(YEAR(DATEVALUE([Book1.xls]Sheet2!M4:M1550)),MONTH(DATEVALUE([Book1.xls]Sheet2!M4:M1550)),1)<DATE(YEAR(DATEVALUE([Book1.xls]Sheet2!X4:X1550)),MONTH(DATEVALUE([Book1.xls]Sheet2!X4:X1550)),1)

I get the impression that your dates only look like dates, but are actually
strings.

HTH,
Bernie
MS Excel MVP

 

[Gos-C]

It's fustrating . . . but, after trying with one row only and then
different ranges, I have been able to narrow the problem down to this:

=SUMPRODUCT(--([Book1.xls]Sheet2!C4545:C13550="AB"),--([Book1.xls]Sheet2!M4545:M13550<>""),--(DATE(YEAR([Book1.xls]Sheet2!M4545:M13550),MONTH([Book1.xls]Sheet2!M4545:M13550),1)<DATE(YEAR([Book1.xls]Sheet2!X4545:X13550),MONTH([Book1.xls]Sheet2!X4545:X13550),1)),[Book1.xls]Sheet2!R4545:R13550)

works as indicated, but not if I include any or all of rows 4 to 4544
(if I do, I get the #VALUE! error). I formatted the rows again but
without any success.

(The full range of my data is A4:X13550).

Any idea what is causing this problem and how I can fix it?

Really appreciate your help.

Gos-C

 

[Bernie Deitrick]

Gos-C,

After you have reformatted those cells, you need to force Excel to recognize the strings as dates.
Applying the format doesn't do it by itself.

Simply type a 1 into an empty cell, copy that cell, then select the cells M4:M4544 and X4:X4544 and
then choose Edt / Pastespecial / Values & Multiply. That will convert the strings to dates that
will work with your formulas.

HTH,
Bernie
MS Excel MVP

 

[Gos-C]

BRAVO-O-O-O-O!

Thanks a million, Bernie.

Gos-