CountIf Not Multiple

[Dax Arroway]

I'm trying to count a column that doesn't match a few selections. For
example, I want to count all cells in column A which doesn't contain blue,
green, or purple.

So my formula would be something like:
=CountIf(A1:A20, <>blue or green or purple)

Any help please?
Thanks in advance!
--Dax

 

[xlmate]

try this formula

Assuming you have a header row in row 1,
place this in B2 and copy down to as far as where your data in col A end.


=SUMPRODUCT(--(A2:A10<>"blue")*--(A2:A10<>"red")*--(A2:A10<>"purple"))

Does this do waht you want?

HTH
--
======================
Pls click Yes if this has help you
======================

Thank You

cheers,

 

[Dax Arroway]

This actually doesn't work because I'm guessing that some of the cells in the
array (A2:A20) are populated by another sheet and some of the values are not
there yet (displayed as #NA).

The other "counters" I've got count the blues, purples, etc. and I'm looking
for a formulat that "Counts everything else except those I already counted."

My other counters are: =Count(A2:A20, "blue") which works great! But
getting it to NOT count 3 different things, is the hard part.

 

[Dax Arroway]

Isn't there a way of counting everything BUT thing1, thing2, and thing3?

 

[xlmate]

Hi
Do you want to count cells with "blue","green" and "purple"?
From what I gather, you do NOT want to count cells with these.

The formula that I have provide does NOT count cells with these texts.


--

cheers,

 

[Fred Smith]

If you want "Counts everything else except those I already counted", just do
it the way you said, as in:

=count(A2:A20)-countif(a2:a20,"blue")-countif(a2:a20,"red")-countif(a2:a20,"purple")

Regards,
Fred.

 

[xlmate]

Dax

The 2 formulas provided does count the total on those cells that doesn't
contain
the words "green", "purple" and "red" in a range.

If the result you get are not what you want, then maybe we don't understand
what you are trying to achieve.

You will need to elaborate more and give an example, like providing how does
your table look like and the result you want to see in order for us to assist.

--

cheers,