Hi

When I check the below formula it seems to fall over because of the use
of the Column function.

SUMPRODUCT((
OFFSET(mod_Records_Filtered_AccountCodeCol,1,,43,) =
CashFlow_Primary!$E12)*
(OFFSET(mod_Records_Filtered_AccountCodeCol,1,COLU MN(Records_Filtered!AC$1)-COLUMN(mod_Records_Filtered_AccountCodeCol),43,)))

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

Thanks in advance

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

Peter,

I haven't tested it as I don't have the data clear in my mind, but try

=SUMPRODUCT(N(OFFSET(mod_Records_Filtered_AccountC odeCol,1,,43,)=
CashFlow_Primary!$E12)*
N(OFFSET(mod_Records_Filtered_AccountCodeCol,1,COL UMN(Records_Filtered!AC$1)
-COLUMN(mod_Records_Filtered_AccountCodeCol),43,)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PeterW" wrote in
message ...

Hi

When I check the below formula it seems to fall over because of the use
of the Column function.

SUMPRODUCT((
OFFSET(mod_Records_Filtered_AccountCodeCol,1,,43,) =
CashFlow_Primary!$E12)*

(OFFSET(mod_Records_Filtered_AccountCodeCol,1,COLU MN(Records_Filtered!AC$1)-
COLUMN(mod_Records_Filtered_AccountCodeCol),43,)))

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

Thanks in advance

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

Over-done a bit

=SUMPRODUCT((OFFSET(mod_Records_Filtered_AccountCo deCol,1,,43,)=
CashFlow_Primary!$E12)*
N(OFFSET(mod_Records_Filtered_AccountCodeCol,1,COL UMN(Records_Filtered!AC$1)
-COLUMN(mod_Records_Filtered_AccountCodeCol),43,)))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PeterW" wrote in
message ...

Hi

When I check the below formula it seems to fall over because of the use
of the Column function.

SUMPRODUCT((
OFFSET(mod_Records_Filtered_AccountCodeCol,1,,43,) =
CashFlow_Primary!$E12)*

(OFFSET(mod_Records_Filtered_AccountCodeCol,1,COLU MN(Records_Filtered!AC$1)-
COLUMN(mod_Records_Filtered_AccountCodeCol),43,)))

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

Thanks in advance

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

Hi Bob

Thanks for your reply, however for the life of me I can't get that to
work.

Putting the "N" function in front of the "Offset" seems to turn the
whole of the second part of the formula into a single number, whereas
is needs to be an array.

I tried adding the "N" in front of the "Column" function, but no luck
with this either.

Any other ideas would be greatly appreciated

Many thanks

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

Peter,

As I said, testing was a problem, it is not a trivial formula, and I have no
idea of the data. Can you post me the workbook to look at

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PeterW" wrote in
message ...

Hi Bob

Thanks for your reply, however for the life of me I can't get that to
work.

Putting the "N" function in front of the "Offset" seems to turn the
whole of the second part of the formula into a single number, whereas
is needs to be an array.

I tried adding the "N" in front of the "Column" function, but no luck
with this either.

Any other ideas would be greatly appreciated

Many thanks

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

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),43,)))

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,))

Thanks Harlan... that is perfect. I figured out it was returning the
array, just couldn't work out the way to turn the array into a number..
seems obvious in hindsight.

Also, many thanks Bob for your suggestions

The final working formula is

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


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=502377

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,))

Bob Phillips wrote...
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

Functions that *expect* range references rather than arbitrary type
arguments can use arrays of range references. SUMIF and COUNTIF are the
prime examples. N can also, *but* N is a legacy function meant to
simulate the behavior of Lotus 123's @N function, so when given a range
reference or an array of range references, it returns *only* the value
of the 1st cell in each range reference rather than the values of all
cells in the range references. Therefore, N(OFFSET(...)) is only useful
when each of the range references in the apparent array returned by
offset is single cell. That's not the case with the OP's OFFSET calls,
which return a *single* range reference that happens to span 43 rows,
so wrapping it in N would return only the value of the 1st cell in that
single 43 row range.

Why SUM handles this is that it's the simplest (and shortest) way to
convert a single item array into a scalar. N(COLUMN(A1)) still returns
{1}, but SUM(COLUMN(A1)) returns 1 [as does the arguably clearer
INDEX(COLUMN(A1),1,1)].

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

Fair point when the COLUMN(x)-COLUMN(y) expression returns a multilpe
item array. *BUT* you'd still get a hash using

N(OFFSET(r,1,COLUMN(x)-COLUMN(y),43,))

There'd be NO POINT WHATSOEVER to the 4th and 5th args to OFFSET being
anything other than 1 for both. That is, the following is an immutable
identity.

N(OFFSET(r,1,a,43,)) == N(OFFSET(r,1,a,1,1))

where a is COLUMN(x)-COLUMN(y). I guessed that the OP's
COLUMN(x)-COLUMN(y) expression returned a single value and all that was
needed was converting it into a scalar. Looks like I guessed right.

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

Agreed, but the OP's incorrect formula boils down to

=SUMPRODUCT((OFFSET(r,1,,43,)=a)*OFFSET(r,1,COLUMN (x)-COLUMN(y),43,))

When COLUMN(x)-COLUMN(y) returns a multiple item array this would only
make sense (to me at least) if the range reference r refers to a single
column range. In that case, the second OFFSET call is ultimately meant
to return a nontrivial 2D array, in which case

=SUMPRODUCT((OFFSET(r,1,,43,)=a)
*OFFSET(r,1,SUM(COLUMN(x)-COLUMN(INDEX(y,1,1))),43,COLUMNS(y)))

which would still not need an N function call. N would only be needed
if you rewrote the formula as

=SUMPRODUCT((OFFSET(r,1,,43,)=a)
*N(OFFSET(r,ROW(1:43),COLUMN(x)-COLUMN(y),1,1)))

Note that making the 2nd arg to the 2nd OFFSET call an array would be
*MANDATORY*.