Sum product of many pairs of columns

[KeenKiwi]

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.

 

[Bob Phillips]

=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:$GG4>0),$K4:$GG4*$L4:
$GH4/365),2)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[KeenKiwi]

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]

Always happy to help a Kiwi <g>

Regards

Bob

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.