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