Let’s say you are a teacher managing a class’s test scores in Excel. You want to identify how many students scored something other than a zero, as zero might indicate that the student was absent or did not complete the test. In this case, COUNTIF can help you.
Using it a lot of time, I have realized that COUNTIF function is best way to count the cells that are not equal to a specific value you define.
You just need to specify the range of values to check and the criteria that you want to test. That’s it
In the following example, you have a list of values in form of numbers and now from here, you need to count the number of values that are not equal to zero.
Formula: COUNTIF Not Equal To
You can use the formula below to count the number of cells in the range from A2 to A15 that contain a number that is not equal to zero.
=COUNTIF(A2:A15,"<>0")- First, you need to enter the COUNTIF function in cell C1 and enter the starting parentheses.
- Next, select the range from which you want to count the values.
- After that, type a comma to move to the next argument.
- In the end, enter “<>0” in the criteria and close parentheses.
When you hit enter, it returns the count of the numbers that are not equal to zero.
As you can see, you have used the symbol <> (Does Not Equal) before the zero. This symbol is a combination of lower than and greater than signs, and when you use both signs collectively, it means “not equals”.
Read Also – How to Count Colored Cells in Excel (Using a Formula)
Here’s How This Formula Works
To understand this formula, you need to split it into two parts:
- (A2:A15): This part of the formula specifies the range the formula needs to check for not equal to.
- “<>0”: This tells the formula what to look for any cell that doesn’t have a zero in it.
Count Cells Not Equal to a Specific Number
Now instead of zero you can also use a number in the condition which we have used in the below formula:
=COUNTIF(A2:A15, "<>10")
Here’s how it works
The range A2:A15 tells Excel to look at the cells from A2 up to A15. <>10″ sets the condition for counting the cells not equal to “10”. The formula goes through each cell in the range A2 to A15 and checks if the value is not equal to 10. Each time this condition is TRUE, Excel counts it.
Count Not Equal to a Text String
The same formula can be used to count the cells where a text values is not equal to. Have a look at the below formula:
=COUNTIF(A:A, "<>Hello")
Here’s how this formula works
This formula counts that how many cells are there in the range A2 to A15 do not have the text “Hello.” It checks each cell in this range (A2:A15) and takes a count if the cell has a different value than “Hello.” The result tells you the number of cells with value other than “Unknown.”
COUNTIF Not Equal to a Date
Same thing goes with a date. If you have a date which you want to use as a criteria. COUNTIF will do that for you. Here’s the formula that you can use:
=COUNTIF(A2:A15, "<>1/1/2024")
This formula checks each cell from A2:A15 to see if the value is not the date January 1, 2024. If a cell contains any other date or value, it is counted. This gives you the total number of cells that do not have the date January 1, 2024.
COUNTIFS Not Equal To
COUNTIFS is useful when you need to apply AND logic when counting not equal to cells. If you want to count cells that are not equal to “10” and also not equal to “20” in the same range of cells.
=COUNTIFS(A:A, "<>10", A:A, "<>20")
This above formula uses the COUNTIFS counts cells which are not equal to two conditions: the cells do not have the number 10, and they do not have the number 20.
Must Read – COUNTIF Less Than / COUNTIF Greater than 0 / OR Logic in COUNTIF
Not Equal Value from a Cell
Whenever I write a formula like this, where I need to specify a value to create a criteria, I always try to use a cell reference. What I mean is, instead of adding the not equal to value directly into the formula, you can enter it into a cell and then refer to that cell in the formula.
This gives you more flexibility when you need to change the value. You don’t need to change the value from the formula, instead you just need to go to the cell and change it from there.
And especially, when you have applied the COUNTIF formula to an entire columns, in that case, using cell reference of a cell can save you a lot of time as you don’t need to go to the each cell and change the formula.