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

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)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

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

--
Regards,
Tom Ogilvy

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>