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.