|
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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 |
Sumproduct Problem
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" wrote in message ... 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 |
Sumproduct Problem
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 -- 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 |
Sumproduct Problem
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" wrote in message ... 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 -- 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 |
Sumproduct Problem
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 -- 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 |
Sumproduct Problem
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" wrote in message ... 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 -- 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 |
Sumproduct Problem
BRAVO-O-O-O-O! Thanks a million, Bernie. 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 |
| All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com