Converting Lotus 1.2.3 V5 DSUM function with conditions to Excel

[Pam]

I have used google to help with this subject and found the SUMPRODUCT
solution works. However, I have a condition which I cannot seem to be
able to figure out. Any help is appreciated.

In Lotus the formula is:
@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.

Thanks

Pam

 

[Harlan Grove]

...
...

In Lotus the formula is:
@IF($C192=0,0,@DSUM($database,"field",fieldname=$A192#AND#AMT>0))

...

Just the DSUM. Try

=SUMPRODUCT(INDEX(Database,0,MATCH("field",INDEX(Database,1,0),0)),
(INDEX(Database,0,MATCH("fieldname",INDEX(Database,1,0),0))=$A192)
*(INDEX(Database,0,MATCH("AMT",INDEX(Database,1,0),0))>0))

 

[onedaywhen]

The Excel AND function works differently. In 123 you might write

fieldname=$A192#AND#AMT>0

but in Excel the equivalent is

AND(fieldname$A192,AMT>0)