Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula help
Trying to match 3 fields in one dataset and return a text value in a 4th field.
=SUMPRODUCT(--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$A2:$A35000=--LEFT(E4,5)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$E2:$E35000=--MID(E4,6,10)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$F2:$F35000=--RIGHT(E4,10)),('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$AD2:$AD35000)) The value I am trying to return in column AD is text. Should I alter the formula because the return is a 0? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula help
SUMPRODUCT won't return TEXT values.
I would use cells to hold: A1: =--LEFT(E4,5) B1: =--MID(E4,6,10) C1: =--RIGHT(E4,10) Then this array formula** (I'm leaving out the path/sheet names so add it to your formula): =INDEX(AD2:AD35000,MATCH(1,(A2:A35000=A1)*(E2:E350 00B1)*(F2:F35000=C1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "ronda120" wrote in message ... Trying to match 3 fields in one dataset and return a text value in a 4th field. =SUMPRODUCT(--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$A2:$A35000=--LEFT(E4,5)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$E2:$E35000=--MID(E4,6,10)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$F2:$F35000=--RIGHT(E4,10)),('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$AD2:$AD35000)) The value I am trying to return in column AD is text. Should I alter the formula because the return is a 0? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct formula help
SUMPRODUCT is for finding the SUM of PRODUCT of two or more arrays...
Use Vlookup or Index/Match to lookup the value. See http://www.contextures.com/xlFunctions03.html for ideas. "ronda120" wrote: Trying to match 3 fields in one dataset and return a text value in a 4th field. =SUMPRODUCT(--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$A2:$A35000=--LEFT(E4,5)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$E2:$E35000=--MID(E4,6,10)),--('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$F2:$F35000=--RIGHT(E4,10)),('[Copy of Private_Active_AFS_Portfolio_200903.xls]Guarantor'!$AD2:$AD35000)) The value I am trying to return in column AD is text. Should I alter the formula because the return is a 0? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT formula help?? | Excel Worksheet Functions | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
SUMPRODUCT formula - help! | Excel Worksheet Functions | |||
SUMPRODUCT formula help | Excel Discussion (Misc queries) | |||
sumproduct formula | Excel Worksheet Functions |