COUNTIF using exclusions

D

djDaemon

I have a column (C) that contains several part numbers (which are comprised
of text and numbers). I want to sum all the occurences of "RY..." cells,
excluding those which end with "...GBMK".

Is there an easy way to do this?
 
R

Ron Coderre

Try something like this:

With a list in A1:A10

B1: =COUNTIF(A1:A10,"RY*")-COUNTIF(A1:A10,"RY*GMBK")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
D

Dave Breitenbach

With below table as sample for a1 thru b5 (and c1 with the following formula:
=SUMPRODUCT(--(LEFT(A1:A5,2)="ry"),--(RIGHT(A1:A5,4)<>"gbmk"),--(B1:B5))

ry4355 6 31
ry453 7
ry346gbmk 8
fr5tgbmk 9
ry4543sf 10


hth,
Dave
 
Top