# How to Count Cells Not Equal To (COUNTIF) in Excel (Formula)

- Written by Puneet

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")``
1. First, you need to enter the COUNTIF function in cell C1 and enter the starting parentheses.
2. Next, select the range from which you want to count the values.
3. After that, type a comma to move to the next argument.
4. 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.

Now as you can see you have used the symbol <> (Does Not Equal) before the zero. This symbol is a combination to lower than and greater than signs and when you use both signs collectively, it means “not equals”.

### 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.

## 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.

Last Updated: May 05, 2024