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


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

just a cursor look but if you have valid dates

=sumproduct((month(daterange)=2)

--
Don Guillett
SalesAid Software

"Gos-C" wrote in
message ...

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


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile:
http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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

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


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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




"Gos-C" wrote in
message ...

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


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile:
http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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

Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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

Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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

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

Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555

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

Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=510555