Sumproduct formula problem

[lhkittle]

Using

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

Where drop downs in:
B2 = uPVC
C2 = 160

And this row exists in the table B816

uPVC 160 Class 16

Formula returns #value

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

Thanks.
Howard

 

[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 B816

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.

 

[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

 

[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))

 

[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.

 

[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