Converting Lotus 1.2.3 V5 DSUM function with multiple conditions to Excel

  • Thread starter Thread starter pb1
  • Start date Start date
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
 
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)))
 
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
 
Scratch that last post. The original Lotus summary was incorrect.
What I did in Excel is fine.

Thank
 
Back
Top