This guide will discuss how to count cells not equal to a value in Excel using the COUNTIF function.
Excel is an excellent tool to use for different situations and purposes. Since it has several built-in functions and tools, we can perform a wide variety of tasks in Excel. For instance, we can utilize the built-in functions in Excel to count specific types of data in our data set.
In this guide, we will focus on learning how to count cells not equal to a value in Excel. So there are several types of counting functions in Excel. For example, we have the COUNT, COUNTIF, and COUNTIFS functions. However, we will only be using the COUNTIF function for this specific task.
When we say we want to count cells not equal to a value, we simply want to count the cells in the data set except for one specific value or data. So we want to exclude that data or value from the total count. Hence, we will simply return the total count minus the value or data we excluded.
Let’s take a sample scenario wherein we need to count cells not equal to a value in Excel.
Suppose you have a data set containing the status of different projects. So there are different stages of the projects such as completed, ongoing and stopped. And you want to count the number of projects left excluding the stopped ones.
To do this, you simply used the COUNTIF function and set the criteria to count the range not equal to stopped. So this will return the total number of projects that are completed and ongoing.
Before we move on to a real example of counting cells not equal to a value in Excel, let’s refresh our memory on the syntax of the COUNTIF function.
The Anatomy of the COUNTIF Function
The syntax or the way we write the COUNTIF function is as follows:
=COUNTIF(range, criteria)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- COUNTIF() refers to our
COUNTIFfunction. And this is used to count the number of cells within a range that match or meet the conditions or criteria we set. - range is a required argument. And this refers to the range of cells from which we want to count the non-blank cells.
- criteria is another required argument. So this refers to the condition, which can be in the form of a number, expression, or text that will define which cells will be counted. And this is where we will input the value we want to exclude from the count.
Great! Now we can dive into a real example of counting cells not equal to a value in Excel.
A Real Example of Counting Cells Not Equal to Value in Excel
Let’s say we have a data set containing the status of the different projects in the company. And there can be three statuses for each project which are completed, ongoing, and stopped. So our initial data set would look like this:
To do this, we can simply utilize the COUNTIF function. So the COUNTIF function will return the count of the cells that match the condition or criteria we set. When we say we want to count cells not equal to a value, we simply mean we want to exclude a specific value from the total count.
Moreover, the COUNTIF function allows us to do this. Since the function is a type of counting function that allows us to set a condition or criteria, the function will only count the values that match our conditions or criteria.
Furthermore, the COUNTIF function supports logical operators and wildcard characters. So we can simply set the condition of excluding a value from the total count using the logical operators.
So logical operators are also known as comparison operators. And these operators are used to compare two values. In Excel, there are six types of logical operators which are equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
In this case, we will only be using one logical operator in our criteria which is not equal to (<>). With this logical operator, we can set the criteria of the COUNTIF function to exclude a specific value from the total count.
Let’s say we want to count cells not equal to the status stopped in our data set. So we can simply set the criteria of the function to “<>Stopped”. And this will return the total count of cells that have the status of completed and ongoing.
Otherwise, we can also count cells not equal to the status completed in our data set. So we only want to return the total count of projects that have the status ongoing and stopped. To do this, we will simply set the criteria to “<>Completed”.
Thus, we can simply apply this method to any type of data set wherein we want to count cells not equal to a value. So we only need to remember to set the criteria to <> plus the value we want to exclude from the total count.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can move on and discuss the process of how to count cells not equal to a value in Excel.
How to Count Cells Not Equal to Value in Excel
In this section, we will discuss the step-by-step process of how to count cells not equal to a value in Excel. Furthermore, each step has details instructions and pictures to help you through the process.
To apply this method in your work, simply follow the steps below.
1. Firstly, we will create a new table to input the count of cells not equal to a specific value. In this case, we will first count cells not equal to the status of completed in our data set.
To do this, we will simply type in the formula “=COUNTIF(C2:C8, “<>Completed”)”. Then, we will press the Enter key to return the count.
2. Secondly, we will now try to count cells not equal to the status of ongoing in our data set. So we will input the formula “=COUNTIF(C2:C8, “<>Ongoing”)”. Lastly, we will press the Enter key to return the result.
3. Lastly, we will now count the cells not equal to the status of stopped in our data set. In this case, we will type in the formula “=COUNTIF(C2:C8, “<>Stopped”)”. Next, we will press the Enter key to return the count.
4. And tada! We have successfully counted cells not equal to a value in Excel.
And that’s pretty much it! We have successfully discussed how to count cells not equal to a value in Excel. Now you can simply apply this method to your work whenever you need to exclude a specific value from the total count.
Are you interested in learning more about what Excel can do? You can now use the COUNTIF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
