Converting Lotus DSUM to Excel SUMIF

[HSNYDER313]

My Lotus function:

@DSUM($LSTDETAIL,"MTG",@LEFT(ACCOUNT,3)="QEA"#AND##NOT#ACCOUNT="QEAX")

My failed Excel function


=SUMIF(LSTDETAIL,"MTG",LEFT(ACCOUNT,3)="QEA"#AND##NOT#ACCOUNT="QEAX")

The criteria section beginning with "QEA" becomes highlighted.

LSTDETAIL is a range.
MTG is a column heading
I want to sum up the amounts in the MTG column for which the first
3 left characters are QEA and all characters are not QEAX in the
LSTDETAIL range.

Any help will be appreciated.

Thanks,

Harvey
Providence, RI

 

[Bob Phillips]

Not absolutely sure, but maybe

=SUMPRODUCT(--(LEFT(ACCOUNT,3)="QEA"),--(ACCOUNT<>"QEAX"),LSTDETAIL)

not sure where MTG comes into it.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Tom Ogilvy]

=Sumif(LSTDETAIL,"QEA*",SomeRange)-Sumif(LSTDETAIL,"QEAX",SomeRange)