Sumproduct formula problem

  • Thread starter Thread starter lhkittle
  • Start date Start date
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
 
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
Click to expand...

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.
 
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
Click to expand...
Regards

Claus B.
Click to expand...

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

Blows me away.

Thanks Clause.
Howard
 
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.
Click to expand...

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))
 
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))
Click to expand...

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


Regards
Claus B.
 
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
Click to expand...

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

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

Howard
 
Back
Top