Intro
Discover how to count cells in Excel that dont contain specific text. Learn 5 easy methods to count non-text values, including using COUNTIF, SUMIF, and filtering functions. Master Excel formulas and functions to compare and count data with precision, and take your spreadsheet skills to the next level.
In Microsoft Excel, counting cells that do not contain a specific text can be a useful skill for data analysis and manipulation. This can be particularly handy when you need to exclude certain values or text from your count, helping you to focus on the data that matters. Here are five ways to count in Excel if the cell is not equal to a specific text:
1. Using COUNTIF Function
The COUNTIF function is one of the most straightforward methods to count cells that do not contain a specific text. The general syntax for this function is COUNTIF(range, criteria), where the range is the range of cells you want to count, and the criteria are the conditions you want to apply.
For instance, if you want to count all cells in column A that do not contain the text "example", you can use the following formula:
=COUNTIF(A:A, "<>example")
This formula works by using the <>' operator, which means "not equal to". Therefore, it counts all cells in column A that are not equal to "example".
Practical Example:
Suppose you have a list of names in column A, and you want to count all names that are not "John". Your list might look like this:
| Name |
|---|
| John |
| Emily |
| John |
| David |
| John |
| Sarah |
Using the COUNTIF function, you can count the names that are not "John" with the formula:
=COUNTIF(A:A, "<>John")
This will return the count of all names in column A that are not "John".
2. Using SUMPRODUCT and NOT Functions
Another way to count cells that do not contain a specific text is by using the SUMPRODUCT and NOT functions together. The general syntax for this approach is:
=SUMPRODUCT(NOT(range="specific text"))
Where range is the range of cells you want to count, and "specific text" is the text you want to exclude from your count.
For example, if you want to count all cells in column A that do not contain the text "example", you can use the following formula:
=SUMPRODUCT(NOT(A:A="example"))
This formula works by using the NOT function to reverse the logic of the comparison, so it counts all cells that do not match the specified text.
Practical Example:
Using the same list of names from the previous example, if you want to count all names that are not "John" using SUMPRODUCT and NOT functions, your formula would be:
=SUMPRODUCT(NOT(A:A="John"))
This formula will give you the count of all names in column A that are not "John".
3. Using FILTER Function (Excel 365 and Later)
If you're using Excel 365 or a later version, you can use the FILTER function to count cells that do not contain a specific text. The general syntax for this function is:
=COUNT(FILTER(range, NOT(range="specific text")))
Where range is the range of cells you want to count, and "specific text" is the text you want to exclude from your count.
For instance, if you want to count all cells in column A that do not contain the text "example", you can use the following formula:
=COUNT(FILTER(A:A, NOT(A:A="example")))
This formula works by using the FILTER function to filter out cells that contain the specified text and then counts the remaining cells.
Practical Example:
Using the same list of names again, if you want to count all names that are not "John" using the FILTER function, your formula would be:
=COUNT(FILTER(A:A, NOT(A:A="John")))
This will give you the count of all names in column A that are not "John".
4. Using COUNTIFS Function
If you need to count cells that do not contain a specific text and also meet other criteria, you can use the COUNTIFS function. The general syntax for this function is:
=COUNTIFS(range1, "<>specific text", [range2], [criteria2],...)
Where range1 is the range of cells you want to count, "specific text" is the text you want to exclude from your count, and [range2] and [criteria2] are additional ranges and criteria you might want to apply.
For example, if you want to count all cells in column A that do not contain the text "example" and are greater than 10, you can use the following formula:
=COUNTIFS(A:A, "<>example", B:B, ">10")
Assuming column B contains numbers you want to evaluate.
Practical Example:
If you have a list of names and ages, and you want to count all names that are not "John" and are over 25, your formula might look like this:
=COUNTIFS(A:A, "<>John", B:B, ">25")
This will count all names in column A that are not "John" and have an age greater than 25 in column B.
5. Using Array Formula
Lastly, you can use an array formula to count cells that do not contain a specific text. The general syntax for this approach is:
=SUM(IF(range<>"specific text", 1, 0))
This formula works by checking each cell in the range to see if it does not contain the specified text. If a cell does not contain the text, it counts as 1; otherwise, it counts as 0.
Practical Example:
Using the same list of names, if you want to count all names that are not "John" using an array formula, your formula would be:
=SUM(IF(A:A<>"John", 1, 0))
After entering this formula, you must press Ctrl+Shift+Enter to convert it into an array formula.
Excel Not Equal To Text Image Gallery
In conclusion, Excel offers several methods to count cells that do not contain a specific text, each with its own set of advantages and situations in which it's most applicable. Whether you prefer the straightforward approach of COUNTIF, the flexibility of SUMPRODUCT, the conditional logic of FILTER, the multipurpose COUNTIFS, or the power of array formulas, there's a solution tailored to your needs.