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)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Gos-C" <Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com> wrote in
message news:Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com...
>
> 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
>

[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" <Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com> wrote in message
news:Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com...
>
> 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
>

[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




"Gos-C" <Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com> wrote in
message news:Gos-C.22ytqn_1139494205.9012@excelforum-nospam.com...
>
> 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
>

[Gos-C]

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]

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" <Gos-C.22ywip_1139497825.2553@excelforum-nospam.com> wrote in message
news:Gos-C.22ywip_1139497825.2553@excelforum-nospam.com...
>
> 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]

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" <Gos-C.22yzzp_1139502312.6652@excelforum-nospam.com> wrote in message
news:Gos-C.22yzzp_1139502312.6652@excelforum-nospam.com...
>
> 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
>

[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" <Gos-C.22zd6n_1139519407.5214@excelforum-nospam.com> wrote in
message news:Gos-C.22zd6n_1139519407.5214@excelforum-nospam.com...
>
> 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
>

[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" <Gos-C.230w1q_1139590526.1952@excelforum-nospam.com> wrote in message
news:Gos-C.230w1q_1139590526.1952@excelforum-nospam.com...
>
> 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
>

[Gos-C]

BRAVO-O-O-O-O!

Thanks a million, Bernie.

Gos-C