Harlan,

Whilst we know that COLUMN and ROW always return arrays, that is often a
useful situation that can be exploited in a formula. That array can be used
to force OFFSET to pass an array to another function. As you say, OFFSET
returns an undocumented result that functions like an array of range
references, not a true array, but other functions can use that array of
range references, N is one such , SUBTOTAL is another. SUM may work in this
case (although how you worked out that the OP needed to SUM them is beyond
me <G), but it won't always be appropriate. For instance

SUM(COLUMN(C1)-COLUMN(A1:B1))

if summed returns a single value 3, and if used in an OFFSET formula such as

=OFFSET(A1,,SUM(COLUMN(C1)-COLUMN(A1:B1)))

returns the cell D1, whereas if using Column in this way

N(OFFSET(A2,,COLUMN(C2)-COLUMN(A2:B2)))

returns an array of range values from the COLUMN part which uses N to return
the values in C1 and B1, which can be passed to SUM or SUMPRODUCT to do its
stuff.

So whilst SUM may work for the OP here, I wouldn't want him to think that is
always the way.

Regards

Bob

"Harlan Grove" wrote in message
oups.com...
PeterW wrote...
When I check the below formula it seems to fall over because of the use
of the Column function.

=SUMPRODUCT((OFFSET(mod_Records_Filtered_AccountC odeCol,1,,43,)
=CashFlow_Primary!$E12)*(OFFSET(mod_Records_Filte red_AccountCodeCol,1,

COLUMN(Records_Filtered!AC$1)-COLUMN(mod_Records_Filtered_AccountCodeCol),4
3,)))

Is it possible to use the Column function with Sumproduct??
...

You're correct that this formula fails is due to the COLUMN function
call. The reason is that COLUMN *always* returns an array (as does ROW)
even when returning a single value. To demonstrate, type =COLUMN(A1)
and press [F9] rather than [Enter]. The result in the formula bar will
be ={1}, not =1. When you pass OFFSET arrays, even single value arrays,
as 2nd or 3rd arguments, OFFSET returns an undocumented results that
functions like an array of range references. Entered directly in an
cell range, Excel would evaluate such formulas as expected, but used as
subexpressions in more complicated formulas they won't work.

The answer is converting the COLUMN subexpression into a true scalar
(single value nonarray), and the easiest way to do that is using SUM.
So try

=SUMPRODUCT(--(OFFSET(mod_Records_Filtered_AccountCodeCol,1,,43, )
=CashFlow_Primary!$E12),OFFSET(mod_Records_Filtere d_AccountCodeCol,1,
SUM(COLUMN(Records_Filtered!AC$1)
-COLUMN(mod_Records_Filtered_AccountCodeCol)),43,))