CountIf Not Multiple

D

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
 
X

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,
 
D

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.
 
X

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,
 
F

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.
 
X

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top