Have you ever been in a condition where you need to count cells in an Excel sheet?
Well, sometimes it is necessary to count cells as per your project’s requirements. And it happens when the COUNTIF not equal to a specific value. For this, Excel has specifically designed some functions that help you count values that are not equal to a specific value. The COUNTIF function is the one and only solution for this problem and here we will discuss it in detail.
First of all, let’s move on to the basic COUNTIF formula:
Basic Formula
=COUNTIF(range, <>Value)
How does the COUNTIF Formula Work?
The basis of this formula is the COUNTIF function, in which the range is needed that help in counting the cells. In the formula, the next argument is the value that is no longer needed in the function. In the COUNTIF formula, all other values are important except this one.
The given condition is solved when the COUNTIF function counts the cells in the range. To count the cells available in the range, the not equal to the operator (<>) is used that never equals to this value.
How to Use COUNTIF to Count Cells Not Equal to X or Y
With the COUNTIF function, you can count cells that meet the criteria. Not equal operator (<>) is used to make a “not equal” logical statement, for instance “<>WATER.”
You need to add range criteria in the function to make an x or y logic. You can even add z logic with x and y. In the given example, you can see the COUNTIF counts cells in range Type(D3:D4) that is not equal to x(“Water”) or y(“FIRE”).
This is how count if not equal to X or Y is done simply.
How to count cells not equal to a specific value
The COUNTIF function is needed to count cells that are not equal to a specific value. Well, this exactly counters the cells that are equal to. In the COUNTIF function, you need to count the number of cells given in the specific range that fulfills the specific criteria. The symbol “<>” is used that represent the condition not equal to a value. Have a look at the following example.
In this example, you can see two conditions; first, the flights counting that didn’t arrive and the second one is the flights that are not rescheduled. This condition is done with the help of the COUNTIF function.
The COUNTIF not equal to a specific value – Formula Explained
=COUNTIF(D5:D10,”<>Arrived”)
At first, you can see the range, D5:D10 following an IF condition. Enter <>Arrived with double quotation marks like “<>Arrived” to find flights that have not arrived. From cells in D5:D10, this can return the count that is not equal to “Arrived”.
Likewise, in the second condition, you will see the flights counting that are not Rescheduled after modifying the criteria to “<>Rescheduled”.
Notice: Remember that the COUNTIF function is not case sensitive at all and the text values in COUNTIF criteria have to be in double quotes like this (“”).
Tips & Tricks: You can avoid errors by using a value in another cell as the criteria or a part of the criteria. Moreover, to concatenate you may use an ampersand (&) when a part of the criteria is in another cell.
Setting Up Data
In the following example, the project information data set is used. Two columns A and B appeared with the names and status as “completed”, “ongoing” and “Stalled”.
Other than the completed cells, to count the cells you have to:
- Go to cell E4
- Apply the formula =COUNTIF($B$2:$B$8,”<>Completed”) to E4.
- Press Enter
Doing this will show the projects counting rather than the completed ones. You can change this formula as per your requirements. Also, note that the COUNTIF function is not case-sensitive that’s why you can make any type of uppercase or lowercase letters combination.
As part of the criteria, you are free to use a value from a cell. For this, the ampersand & is used. In order to solve the previous example, you may need to apply the formula as =COUNTIF(B2:B8,”<>”&B4) to E5.
Doing this will display the projects excluding stalled in E5.
At times, the problem might be more complicated than simply applying the formula or a function. That’s why you always need to be careful every time you are assigning values to your data.
In Excel Countif not equal to condition, you need to consider all the above hacks to use it properly.
COUNTIF Not Equal to X
To apply one condition to the counting formula, you may use COUNTIF. The formula for COUNTIF can be written with data as COUNTIF not equal to cappuccino:
=COUNTIF(E23:E42, “<>cappuccino”)
As you already know COUNTIFS use one compulsory condition and the rest are optional, that’s why you can write this formula as COUNTIFS not equal to cappuccino:
=COUNTIFS(E23:E42, “<>cappuccino”)
To Sum Up
COUNTIF not equal to is a great thing to use in your Excel projects. Counting cells in a data set can be a tiring thing, however, with some simple functions you can make complex tasks easier. That’s why the COUNTIF function helps you in many conditions. And you need to be careful while using Excel functions.
