Table of Contents
The COUNTIF function in Excel is a useful tool for counting the number of cells that meet a specific criteria. However, it can also be used to count the number of cells that do not contain a certain value. This can be achieved by using the “<>” operator in conjunction with the COUNTIF function. By inputting this operator and the desired value, the function will count all cells in the specified range that do not contain the specified value. This can be helpful in identifying the number of cells that do not meet a certain requirement or in troubleshooting data that may be missing certain values.
Excel Formula: COUNTIF Does Not Contain
You can use the following formulas to count the number of cells in a range in Excel that do not contain specific text:
Formula 1: Count Cells that Do Not Contain One Specific Text
=COUNTIF(A2:A11,"<>*A*")
This particular formula counts the number of cells in the range A2:A11 that do not contain “A” anywhere in the cell.
Formula 2: Count Cells that Do Not Contain Multiple Specific Text
=COUNTIFS(A2:A11,"<>*A*", A2:A11, "<>*C*")
This particular formula counts the number of cells in the range A2:A11 that do not contain “A” or “C” anywhere in the cell.
The following examples show how to use each formula in practice with the following list of Product ID’s in Excel:
Example 1: Count Cells that Do Not Contain One Specific Text
Suppose we would like to count the number of cells that do not contain “A” anywhere in the Product ID.
We can type the following formula into cell C2 to do so:
=COUNTIF(A2:A11,"<>*A*")
The following screenshot shows how to use this formula in practice:
From the output we can see that there are 4 cells that do not contain “A” anywhere in the Product ID.
Example 2: Count Cells that Do Not Contain Multiple Specific Text
We can type the following formula into cell C2 to do so:
=COUNTIFS(A2:A11,"<>*A*", A2:A11, "<>*C*")
The following screenshot shows how to use this formula in practice:
From the output we can see that there is only 1 cell that does not contain “A” or “C” anywhere in the Product ID.
Note: You can find the complete documentation for the COUNTIFS function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: