Sum product of many pairs of columns
 

Excel 2002.
I have already implemented 2 great solutions from this forum in my project,
but am going round in circles on the final element.
A macro pulls several ranges of information from a named worksheet in each
file in a specified folder, and deposits it in columns in a new workbook.

In the consolidation worksheet:-

Cell K1 is account name.
Cell K2 is account number
Cell K3 is "Bal"
Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19
Cell L3 is "Rate"
Cell L4:L33 contains the daily interest rate e.g. 0.0575

This is repeated numerous times, so M1:N33 relates to a different account
and so on. The daily balance and rate potentially are unique for each account
and change daily.

In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)), which
sums all the negative balances for 1 April, and B4 does the same for positive
balances.

In C4 and D4 I want to show the sum of the interest products on negative and
positive balances respectively, for 1 April. This can be crudely expressed
as:-

=SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0))

I've seen a few ideas that come close to doing this elegantly, for example
using combinations of sumproduct, mod, column, offset and others I can't
recall, but can't figure it out.

Help from the experts will be hugely appreciated.

Sum product of many pairs of columns
 

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:
$GH4/365),2)

and

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4:
$GH4/365),2)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"KeenKiwi" wrote in message
...
Excel 2002.
I have already implemented 2 great solutions from this forum in my
project,
but am going round in circles on the final element.
A macro pulls several ranges of information from a named worksheet in each
file in a specified folder, and deposits it in columns in a new workbook.

In the consolidation worksheet:-

Cell K1 is account name.
Cell K2 is account number
Cell K3 is "Bal"
Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19
Cell L3 is "Rate"
Cell L4:L33 contains the daily interest rate e.g. 0.0575

This is repeated numerous times, so M1:N33 relates to a different account
and so on. The daily balance and rate potentially are unique for each
account
and change daily.

In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)),
which
sums all the negative balances for 1 April, and B4 does the same for
positive
balances.

In C4 and D4 I want to show the sum of the interest products on negative
and
positive balances respectively, for 1 April. This can be crudely expressed
as:-


=SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0))

I've seen a few ideas that come close to doing this elegantly, for example
using combinations of sumproduct, mod, column, offset and others I can't
recall, but can't figure it out.

Help from the experts will be hugely appreciated.

Sum product of many pairs of columns
 

Mate, that's awesome in the true sense of the word. Thanks so much for your
help Bob. At least I was on the right lines, but still so far away.

"Bob Phillips" wrote:

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:
$GH4/365),2)

and

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4:
$GH4/365),2)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"KeenKiwi" wrote in message
...
Excel 2002.
I have already implemented 2 great solutions from this forum in my
project,
but am going round in circles on the final element.
A macro pulls several ranges of information from a named worksheet in each
file in a specified folder, and deposits it in columns in a new workbook.

In the consolidation worksheet:-

Cell K1 is account name.
Cell K2 is account number
Cell K3 is "Bal"
Cell K4:K33 contains the daily balance (30 days in April) e.g. 72758.19
Cell L3 is "Rate"
Cell L4:L33 contains the daily interest rate e.g. 0.0575

This is repeated numerous times, so M1:N33 relates to a different account
and so on. The daily balance and rate potentially are unique for each
account
and change daily.

In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)),
which
sums all the negative balances for 1 April, and B4 does the same for
positive
balances.

In C4 and D4 I want to show the sum of the interest products on negative
and
positive balances respectively, for 1 April. This can be crudely expressed
as:-


=SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0))

I've seen a few ideas that come close to doing this elegantly, for example
using combinations of sumproduct, mod, column, offset and others I can't
recall, but can't figure it out.

Help from the experts will be hugely appreciated.

Sum product of many pairs of columns
 

Always happy to help a Kiwi <g

Regards

Bob

"KeenKiwi" wrote in message
...
Mate, that's awesome in the true sense of the word. Thanks so much for
your
help Bob. At least I was on the right lines, but still so far away.

"Bob Phillips" wrote:


=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:
$GH4/365),2)

and


=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG40),$K4:$GG4*$L4:
$GH4/365),2)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"KeenKiwi" wrote in message
...
Excel 2002.
I have already implemented 2 great solutions from this forum in my
project,
but am going round in circles on the final element.
A macro pulls several ranges of information from a named worksheet in
each
file in a specified folder, and deposits it in columns in a new
workbook.

In the consolidation worksheet:-

Cell K1 is account name.
Cell K2 is account number
Cell K3 is "Bal"
Cell K4:K33 contains the daily balance (30 days in April) e.g.
72758.19
Cell L3 is "Rate"
Cell L4:L33 contains the daily interest rate e.g. 0.0575

This is repeated numerous times, so M1:N33 relates to a different
account
and so on. The daily balance and rate potentially are unique for each
account
and change daily.

In A4 I have =SUMPRODUCT(($K$3:$GH$3="Bal")*($K4:$GH4<=0),($K4: $GH4)),
which
sums all the negative balances for 1 April, and B4 does the same for
positive
balances.

In C4 and D4 I want to show the sum of the interest products on
negative
and
positive balances respectively, for 1 April. This can be crudely
expressed
as:-



=SUM(IF(K4<=0,K4*L4/365,0),IF(M4<=0,M4*N4/365,0)...IF(GG4<=0,GG4*GH4/365,0))

I've seen a few ideas that come close to doing this elegantly, for
example
using combinations of sumproduct, mod, column, offset and others I
can't
recall, but can't figure it out.

Help from the experts will be hugely appreciated.