sumproduct lookup #value! (frannnk, oh frannkkkk)

[Guest]

Hi all (and Frank),

The following returns a #value! error:


=SUMPRODUCT(--($D4=Sheet2!$B$1),--($B4=Sheet2!$A$3:$A$155),(Sheet2!$B$3:$B$155))


Column D consists of words (product1, product2, product3)
Sheet2!$B$1 refers to a word (product1)

Column B consists of dates in m,d,yyyy date format.
Sheet2!$A$3:$A155 consists of dates in m,d,yyyy date format.
Labled date skip a day fairly often.

Sheet2!$B$3:$B$155) consists of prices

Essentially I am trying to look up a price that matches with a specified date and product. I can't figure out what I am doing wrong to get a #value! error.

Thanks for your help!

 

[Frank Kabel]

Hi
what yre you trying to do with your first condition ?:
$D4=Sheet2!$B$1

maybe you meant:
=SUMPRODUCT(--($D4=Sheet2!$B$3:$B$155),--($B4=Sheet2!$A$3:$A$155),(Shee
t2!$B$3:$B$155))

or you meant
=($D4=Sheet2!$B$1)*(SUMPRODUCT(--($B4=Sheet2!$A$3:$A$155),(Sheet2!$B$3:
$B$155)))

 

[Guest]

One more thing:

Frequency of dates on Sheet1 ColumnB do not match frequench of Sheet2 ColumnA dates. For example:

Please also note that I omitted "sheet1" from my original post

Sheet1 ColumnB Sheet2ColumnA
1/5/04 1/2/04
1/5/04 1/3/04
1/5/04 1/4/04
1/7/04 1/5/04
1/7/04 1/6/04
1/7/04 1/7/04
1/12/04 1/8/04
....gaps continue no gaps ...to 1/31/04

 

[hgrove]

Steve wrote...

The following returns a #value! error:

=SUMPRODUCT(--($D4=Sheet2!$B$1),
--($B4=Sheet2!$A$3:$A$155),(Sheet2!$B$3:$B$155))

...

Of course it does. Read online help for the SUMPRODUCT function
specifically the first bullet point under Syntax.

You need to move the first term *outside* of SUMPRODUCT. Better still
use an IF to restrict calling SUMPRODUCT with only the last two term
when the first term returns TRUE; else return 0

 

[Guest]

Harlan, I am also honored by your response. I added an if statement like you suggested (see below) and will checkout online help for sumproduct. Thanks for this post and so many of your others. Regards, Steve

=IF(($D4=Sheet2!$B$1)*(SUMPRODUCT(--($B4=Sheet2!$A$3:$A$155),(Sheet2!$B$3:$B$155)))=0," ",(($D4=Sheet2!$B$1)*(SUMPRODUCT(--($B4=Sheet2!$A$3:$A$155),(Sheet2!$B$3:$B$155)))))

 

[hgrove]

Steve wrote...

. . . I added an if statement like you suggested (see below) and
will checkout online help for sumproduct. . . .

=IF(($D4=Sheet2!$B$1)
*(SUMPRODUCT(--($B4=Sheet2!$A$3:$A$155),
(Sheet2!$B$3:$B$155)))=0," ",
(($D4=Sheet2!$B$1)
*(SUMPRODUCT(--($B4=Sheet2!$A$3:$A$155),
(Sheet2!$B$3:$B$155)))))

First, if you want nothing displayed for some condition, you'll hav
FAR FEWER headaches using "" rather than " ". They'll *always* displa
the same, but it's easier to work with "" in downstream formulas.

While I understand what you're trying to do, I think you shouldn't hid
the potential diagnostic of a zero return value when D4 equals Sheet2!B
and there are some entries in Sheet2!A3:A155 equal to B4 but th
corresponding prices in Sheet2!B3:B155 aren't positive numbers. So
think you should shrink this to

=IF(AND($D4=Sheet2!$B$1,COUNTIF(Sheet2!$A$3:$A$155,$B4)),
SUMIF(Sheet2!$A$3:$A$155,$B4,Sheet2!$B$3:$B$155),""

 

[Guest]

In all seriousness...

Short version:
Very interesting and informative Harlan, thank so much. While there are no negative numbers in this specific case, I will apply the formula and remember it for future use.

Long version:
Maybe it's sad, or just nerdy, but for me learning about these new formulas is like finding an overlooked christmas present of in my stocking. Ken Kesey would have been blown away by these psychedelic formulas you and others are providing us. Thanks Harlan, You are the man.

=IF(AND($D4=Sheet2!$B$1,COUNTIF(Sheet2!$A$3:$A$155,$B4)),
SUMIF(Sheet2!$A$3:$A$155,$B4,Sheet2!$B$3:$B$155),"")

separate topic:
If you are up to it, I have a separate VBA issue for you...

http://www.microsoft.com/office/com...d9-8feae6d75298&catlist=&dglist=&ptlist=&exp=