Exceljet

Quick, clean, and to the point

Count cells not equal to many things

Excel formula: Count cells not equal to many things
Generic formula 
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,things))
Explanation 

To count cells not equal to many things (i.e. not equal to x, y, z, etc.), you can use a formula based on COUNTIF, SUMPRODUCT, and COUNTA.

In the example shown, the formula in H5 is:

=COUNTA(B4:C9)-SUMPRODUCT(COUNTIF(B4:C9,things))

Context

Normally, if you have just a couple things you don't want to count, you can COUNTIFS like this:

=COUNTIFS(range,"<>apple",range,"<>orange")

But this doesn't scale very well if you have a list of many things, because you'll have to add an additional range/criteria pair to for each thing you don't want to count. It would be a lot easier to build a list and pass in a reference to this list as part of the criteria. That's exactly what the formula on this page does.

How this formula works

This formula uses the named range "things" (E5:E7) to hold values we don't want to count.

We start by counting all values in the range being counted with COUNTA:

=COUNTA(B4:C9) 

Next, we generate a count of all things we don't want to count with COUNTIF like this:

COUNTIF(B4:C9,things)

Because the named range "things" contains multiple values, COUNTIF will return multiple results. In the example, we get back an array of values like this:

{1;2;2}

and we use SUMPRODUCT to sum all items in the array, which returns 5.  This number is then subtracted from the original total to yield the final result.

Using SUMPRODUCT instead of SUM avoids the need to use array formula syntax.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

I have relied on your website on countless occasions throughout the course of my graduate level finance internship. Compared to a number of unnamed Excel tutorial sites and forums, yours is par-none. It is invariably the only source for consistently accurate and understandable formula functions and syntaxes. I say that as this page has solved an hours-long problem facing me during an all-night work session. Thanks a lot! - Nick
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course