How to negate countif

Question

I am using countif quite often. However, I was wondering if I want to look for something not like:

=Countif(A:A;"1"), which counts all 1 but I want to count in which cell there is NO 1.

I appreciate your answer!

Answer 1

Just:

=COUNTIF(A:A;"<>1")

For excluding also empty cells use COUNTIFs (works in Excel 2007 and later):

=COUNTIFs(A:A;"<>1";A:A;"<>")

for Excel 2003:

=SUMPRODUCT((A:A<>"")*(A:A<>1))