| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Using LOOKUP function.
I hope someone can help me on this.
I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
What's in COA!Q3:Q14?
"luchshel" wrote: I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
Barb,
A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
Maybe something like this array formula?:
=SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF(D epartmentID=$C8,CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT ,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG ,ASEP)),0),0)) Note: Since text wrap may impact the display, there are no space in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
Actually, this regular formula may be easiser:
=IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
I guess your assumption is that the defined names: AccountID,
BusinessUnitID and DepartmentID are *single* cells? Bet'cha an adult beverage they're arrays! Sumproduct Biff "Ron Coderre" wrote in message ... Actually, this regular formula may be easiser: =IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
The defined names are arrays. Currently 824 rows.
"T. Valko" wrote: I guess your assumption is that the defined names: AccountID, BusinessUnitID and DepartmentID are *single* cells? Bet'cha an adult beverage they're arrays! Sumproduct Biff "Ron Coderre" wrote in message ... Actually, this regular formula may be easiser: =IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
I was pretty much concentrating on the right side of the formula, but as Biff
suspected and luchshel confirmed, all of the named ranges are multi-cell arrays. Consequently.....How about this regular formula?: =SUMPRODUCT((AccountID=$J$1)*(BusinessUnitID=$J$2) *(DepartmentID=$C8)*(CHOOSE(MATCH(I3,COA!N3:N14,0) ,AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL ,AAUG,ASEP))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: The defined names are arrays. Currently 824 rows. "T. Valko" wrote: I guess your assumption is that the defined names: AccountID, BusinessUnitID and DepartmentID are *single* cells? Bet'cha an adult beverage they're arrays! Sumproduct Biff "Ron Coderre" wrote in message ... Actually, this regular formula may be easiser: =IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
A debt is owed to you my friend.
Everyting appears to be in working order. Many thanks for your assistance Ron. I'll cover Ron on the adult beverage. Thank you. C "Ron Coderre" wrote: I was pretty much concentrating on the right side of the formula, but as Biff suspected and luchshel confirmed, all of the named ranges are multi-cell arrays. Consequently.....How about this regular formula?: =SUMPRODUCT((AccountID=$J$1)*(BusinessUnitID=$J$2) *(DepartmentID=$C8)*(CHOOSE(MATCH(I3,COA!N3:N14,0) ,AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL ,AAUG,ASEP))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: The defined names are arrays. Currently 824 rows. "T. Valko" wrote: I guess your assumption is that the defined names: AccountID, BusinessUnitID and DepartmentID are *single* cells? Bet'cha an adult beverage they're arrays! Sumproduct Biff "Ron Coderre" wrote in message ... Actually, this regular formula may be easiser: =IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array Formula Using LOOKUP function.
Thanks for the feedback; I'm so glad you found that helpful.......
BTW...my beverage of choice is "coffee milk". (It's a regional thing) *********** Regards, Ron XL2002, WinXP "luchshel" wrote: A debt is owed to you my friend. Everyting appears to be in working order. Many thanks for your assistance Ron. I'll cover Ron on the adult beverage. Thank you. C "Ron Coderre" wrote: I was pretty much concentrating on the right side of the formula, but as Biff suspected and luchshel confirmed, all of the named ranges are multi-cell arrays. Consequently.....How about this regular formula?: =SUMPRODUCT((AccountID=$J$1)*(BusinessUnitID=$J$2) *(DepartmentID=$C8)*(CHOOSE(MATCH(I3,COA!N3:N14,0) ,AOCT,ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL ,AAUG,ASEP))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: The defined names are arrays. Currently 824 rows. "T. Valko" wrote: I guess your assumption is that the defined names: AccountID, BusinessUnitID and DepartmentID are *single* cells? Bet'cha an adult beverage they're arrays! Sumproduct Biff "Ron Coderre" wrote in message ... Actually, this regular formula may be easiser: =IF(AND(AccountID=$J$1,BusinessUnitID=$J$2,Departm entID=$C8),SUM(CHOOSE(MATCH(I3,COA!N3:N14,0),AOCT, ANOV,ADEC,AJAN,AFEB,AMAR,AAPR,AMAY,AJUN,AJUL,AAUG, ASEP)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "luchshel" wrote: Barb, A list of twelve text entries equal to range names I have defined in the workbook. AOCT ANOV ADEC AJAN AFEB AMAR AAPR AMAY AJUN AJUL AAUG ASEP "Barb Reinhardt" wrote: What's in COA!Q3:Q14? "luchshel" wrote:Barb I hope someone can help me on this. I am using a third party software on top of an Excel Workbook. This software does not support the use of the Excel function INDIRECT. The following formula works in my Excel model return the expected value of 201. ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,INDIRECT(LOOKUP(I3,COA!N3:N14,COA !Q3:Q14)),0),0),0))} Unfortunately, when I remove the INDIRECT function from the formula a value of ZERO (0) is returned. So, ={SUM(IF(AccountID=$J$1,IF(BusinessUnitID=$J$2,IF( DepartmentID=$C8,LOOKUP(I3,COA!N3:N14,COA!Q3:Q14), 0),0),0))} Doesn't work. I have tried everything I can think of. The problem I have is the data I wish to SUM in the SUM(IF formula is a variable column reference based upon separate criteria. Any ideas would be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix Math using LOOKUP inside Array {} Function | Excel Worksheet Functions | |||
| Variable Table Array in Lookup Function | Excel Worksheet Functions | |||
| Array formula lookup | Excel Worksheet Functions | |||
| Lookup Array Formula | Excel Worksheet Functions | |||
| Two-Way Lookup Array Formula | Excel Worksheet Functions | |||
Linear Mode
