Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with a Sumproduct formula | Excel Discussion (Misc queries) | |||
SumProduct Formula Problem | Excel Discussion (Misc queries) | |||
Sumproduct formula problem | Excel Worksheet Functions | |||
Problem with SUMPRODUCT formula | Excel Worksheet Functions | |||
Problem w/ A Sumproduct Formula | Excel Worksheet Functions |