SUMIF other related field contains certain information
 

In Excel:
6399M208 $100,000.
6399M210 $ 25,000.
6399M208 $ 75,000.
How can I Sum dollars with they are "99" and "08" ?
The Plain SUMIF doesn't seem to yield expected result ($175,000.)
Thanks,
SUNAN

Hi Sunan,

Try this:

=SUMIF(A1:A100,"??99??08",B1:B100)

or

=SUMIF(A1:A100,"*99*08",B1:B100)

Regards,
KL


"Sunantoro" wrote in message
...
In Excel:
6399M208 $100,000.
6399M210 $ 25,000.
6399M208 $ 75,000.
How can I Sum dollars with they are "99" and "08" ?
The Plain SUMIF doesn't seem to yield expected result ($175,000.)
Thanks,
SUNAN

Hi Sunan
One way
=SUMPRODUCT(--(MID($A$1:$A$100,3,2)="99"),--(RIGHT($A$1:$A$100,2)="08"),$B$1:$B$100)

change ranges to suit.


Regards

Roger Govier


Sunantoro wrote:
In Excel:
6399M208 $100,000.
6399M210 $ 25,000.
6399M208 $ 75,000.
How can I Sum dollars with they are "99" and "08" ?
The Plain SUMIF doesn't seem to yield expected result ($175,000.)
Thanks,
SUNAN

Another alternative
=SUMPRODUCT(--(SUBSTITUTE($A$1:$A$100,"M2","")="639908"),$B$1:$B $100)

Regards

Roger Govier


Roger Govier wrote:
Hi Sunan
One way
=SUMPRODUCT(--(MID($A$1:$A$100,3,2)="99"),--(RIGHT($A$1:$A$100,2)="08"),$B$1:$B$100)


change ranges to suit.


Regards

Roger Govier


Sunantoro wrote:

In Excel:
6399M208 $100,000.
6399M210 $ 25,000.
6399M208 $ 75,000.
How can I Sum dollars with they are "99" and "08" ?
The Plain SUMIF doesn't seem to yield expected result ($175,000.)
Thanks,
SUNAN