Sumproduct formula problem
 

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

Sumproduct formula problem
 

Hi Howard,

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

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$1 6,0))
and array-enter this formula with CRTL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Sumproduct formula problem
 

try:

=INDEX($D$8:$D$16,MATCH(B2&C2,$B$8:$B$16&$C$8:$C$1 6,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

Sumproduct formula problem
 

On Friday, October 11, 2013 12:38:44 PM UTC-7, Claus Busch wrote:
Hi Howard,



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



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$1 6,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))

Sumproduct formula problem
 

Hi Howard,

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

=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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Sumproduct formula problem
 

On Friday, October 11, 2013 1:13:22 PM UTC-7, Claus Busch wrote:
Hi Howard,



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



=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


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

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

Howard