***Need Help w/ "COUNTIF" Formula

[Stubbi]

I'm new to the board and could use some help here...I have a column that is populated with 3 letter codes IE.. ABC,DEF,GHI etc.. I am using =COUNTIF(D871:D893,"ABC") to return all cells in the column that = ABC and others.. I want to be able to count all the other 3 letter codes besides ABC,DEF,GHI.. And am having a world of trouble...Can anyone help.
In laymans terms this is what I would like.
=COUNTIF(D871:D893 not equal to ABC,DEF,GHI)

Thanks

 

[Aladin Akyurek]

On 2002-09-06 11:53, Stubbi wrote:
I'm new to the board and could use some help here...I have a column that is populated with 3 letter codes IE.. ABC,DEF,GHI etc.. I am using =COUNTIF(D871:D893,"ABC") to return all cells in the column that = ABC and others.. I want to be able to count all the other 3 letter codes besides ABC,DEF,GHI.. And am having a world of trouble...Can anyone help.
In laymans terms this is what I would like.
=COUNTIF(D871:D893 not equal to ABC,DEF,GHI)

Thanks

=SUMPRODUCT(NOT(ISNUMBER(MATCH(D871:D893,{"ABC","DEF","GHI"},0)))+0)

You can also have ABC, DEF, etc. in a range like A1:C1 and use that range instead of {"ABC","DEF","GHI"}.

 

[Corticus]

Another variation:

=COUNTA(A:A)-COUNTIF(A1:A20,"=ABC")-COUNTIF(A1:A20,"=DEF")-COUNTIF(A1:A20,"=GHI")

 

[Stubbi]

Eureka....It works...Thanks for the help....

Have a great weekend...

S