Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly