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.