Sumproduct formula problem

L

lhkittle

Using

=SUMPRODUCT(B8:B16=B2)*(C8:C16=C2)*(D8:D16)

Where drop downs in:
B2 = uPVC
C2 = 160

And this row exists in the table B8:D16

uPVC 160 Class 16

Formula returns #value

Googled examples would have me believe "Class 16" should be returned.

Thanks.
Howard
 
C

Claus Busch

Hi Howard,

Am Fri, 11 Oct 2013 12:32:05 -0700 (PDT) schrieb (e-mail address removed):
Where drop downs in:
B2 = uPVC
C2 = 160

And this row exists in the table B8:D16

uPVC 160 Class 16

try:
=INDEX($D$8:$D$16,MATCH(B2&C2,$B$8:$B$16&$C$8:$C$16,0))
and array-enter this formula with CRTL+Shift+Enter


Regards
Claus B.
 
L

lhkittle

try:

=INDEX($D$8:$D$16,MATCH(B2&C2,$B$8:$B$16&$C$8:$C$16,0))

and array-enter this formula with CRTL+Shift+Enter
Regards

Claus B.

Indeed, works perfect. I have never seen "B2&C2" along with "Range & Range".

Blows me away.

Thanks Clause.
Howard
 
L

lhkittle

Hi Howard,



Am Fri, 11 Oct 2013 12:32:05 -0700 (PDT) schrieb (e-mail address removed):








try:

=INDEX($D$8:$D$16,MATCH(B2&C2,$B$8:$B$16&$C$8:$C$16,0))

and array-enter this formula with CRTL+Shift+Enter





Regards

Claus B.

Can I assume the table must be on the same sheet to work, as this returns #Value using an identical table in a slightly different range from Sheet1 while the formula and drop down are on sheet2.

Or is it an issue with the MATCH(B2&C2,

Howard

=INDEX(Sheet1!$A$2:$A$10,MATCH($B$2&$C$2,Sheet1!$B$2:$B$10&Sheet1!$C$2:$C$10,0))
 
C

Claus Busch

Hi Howard,

Am Fri, 11 Oct 2013 13:07:06 -0700 (PDT) schrieb (e-mail address removed):
=INDEX(Sheet1!$A$2:$A$10,MATCH($B$2&$C$2,Sheet1!$B$2:$B$10&Sheet1!$C$2:$C$10,0))

the formula must work. Have you forgotten to array-enter the formula?


Regards
Claus B.
 
L

lhkittle

Hi Howard,



Am Fri, 11 Oct 2013 13:07:06 -0700 (PDT) schrieb (e-mail address removed):






the formula must work. Have you forgotten to array-enter the formula?





Regards

My bad, I had the ranges amok. Yes, it works!

Thanks again, sorry for the lazy troubleshooting on my part.

Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top