Converting Lotus 1.2.3 V5 DSUM function with multiple conditions to Excel

P

pb1

I have used the SUMPRODUCT example given by Harlan to mimick a Lotu
1-2-3 formula that used DSUM.

For example, in Excel I have used:

SUMPRODUCT((COUNTIF($A10:$B10,QUARTER)>0)*(INDEX(QUARTER,0,MATCH("DEAL",QUARTER_COLUMN_NAME,0))=$D10
* INDEX(QUARTER,0,MATCH("BALANCE",QUARTER_COLUMN_NAME,0)))

to replace the this Lotus Formula:
@DSUM($QUARTER,"BALANCE",D9=DEAL#AND#(A9=SERNUM#OR#B9=SERNUM))

Now I need to replace this Lotus:
@IF($C192=0,0,@DSUM($database,"field",fieldname=$A192#AND#AMT>0))

I can handle the if part,however it is the AND condition of AMT > 0
that I am having trouble with. Perhaps it is because I am still tryin
to grasp the solution given by Harlan on google. I am in the proces
of reading a post here by Harlan in which he breaks things down for
user.

Any help with the formula above formula is appreciated.

Thanks

Pa
 
P

pb1

I was looking at this too long. I looked at Harlan's example on googl
again and I was able to solve the problem.

Here is what I used:

=SUMPRODUCT((INDEX(LOANVALUES,0,MATCH("DEAL",LOANINFO_COLUMN_NAME,0))=$A2)*
(INDEX(LOANVALUES,0,MATCH("CURAMT",LOANINFO_COLUMN_NAME,0))>0) *
(INDEX(LOANVALUES,0,MATCH("CALCPI",LOANINFO_COLUMN_NAME,0)))
 
P

pb1

Now I have a new problem.

When I sum the column with the results from the above formula, the su
is not equal to what it should. Any ideas?

Thanks again

Pa
 
P

pb1

Scratch that last post. The original Lotus summary was incorrect.
What I did in Excel is fine.

Thank
 
Top