How To Count All Cells Except A Certain Value In Excel

In this article, I will tell you how to count cells that are equal to or not equal to a text value. The comparison contains exact compare, partial compare, case sensitive compare, and case insensitive compare. I will also tell you how to count all cells except a certain value in Excel at the end of the article.

1.  How To Count Cells That Equal To A Text Value.

1.1 Case Insensitive Compare.

1.1.1 Exactly Equal To.

1. We can use the Excel function COUNTIF to create a formula to implement exact and case-insensitive comparisons to text value.
2. The function COUNTIF(range, criteria) contains 2 parameters, the first one is the cell range, and the second one is the text compare criteria.
3. The function COUNTIF is case-insensitive by default.
4. Below are the example data cells in this article. Column A‘s cells contain all the user name text.
   
5. We input the formula =COUNTIF(A2:A6, C2) in cell D2.
6. Then when we input a name text such as jerry Li in cell C2, it will display number 2 in cell D2.
7. This is because there are 2 cells containing text jerry li which use different character cases.
8. This means the comparison is case-insensitive.
9. When we input a name text jerry in cell C2, it will display the number 0 in cell D2.
10. This means the comparison is exact.

1.1.2 Partially Equal To.

1. We use the same data cell example as section 1.1.1.
2. To compare partial cell text to a text value ( that means cell text contains the text value ) we can use the asterisk in the COUNTIF function’s criteria parameter to implement the fuzzy compare.
3. We input the same formula =COUNTIF(A2:A6, C2) in cell D2.
4. Input the name text *SUN in cell C2, then it will display the number 1 in cell D2.
5. This is because cell A5 contains the text Tom Sun.
6. That means the comparison is case insensitive and partially.

1.2 Case Sensitive Compare.

1.2.1 Exactly Equal To.

1. We use the same data cells example in section 1.1.1.
2. We use the Excel functions SUMPRODUCT and EXACT(value, range) together to write the below formula.
3. Input the formula =SUMPRODUCT(–EXACT(C3,A2:A6)) in cell D3.
4. Then when you input text jerry li in cell C3, it will display number 1 in cell D3.
5. When you input text jerry Li in cell C3, it will display the number 0 in cell D3.
6. This means the comparison is case sensitive and exact.

2.  How To Count Cells That are Not Equal To A Text Value.

2.1 Case Insensitive Compare.

2.1.1 Exactly Not Equal To.

1. The text string “<>” means not.
2. Input the formula =COUNTIF(A2:A6, “<>”&C4) in cell D4.
3. Then when you input the text jerry Li in cell C4, it will show the number 3 in cell D4.
4. Because there are 3 cells that do not contain the text jerry Li and case insensitive.

2.1.2 Partially Not Equal To.

1. Input the text *MA* in cell C4.
2. When you press the enter key, it will show the number 4 in cell D4.
3. This is because there are 4 cells that do not contain the text MA case insensitive.

3. How To Count All Cells Except A Certain Value In Excel.

3.1 Case Insensitive Compare.

1. You can use the Excel function COUNTIF to count all cells except a certain value in Excel.
2. You can use the “<>” operator to exclude certain text values.
3. You can see section 2.1.1 for the example.

3.2 Case Sensitive Compare.

1. You can use the EXACT function to get the cell number that contains a certain value.
2. Then use the total cell number minus the above number to get the cell number that does not contain the certain value exactly.
3. You can input the formula = ROWS(A2:A6) -SUMPRODUCT(–EXACT(C6,A2:A6)) in cell D6.
4. Then when you input text jerry li in cell C6, it will display number 4 in cell D6.
5. This is because there is only one cell A3 that contains the text jerry li.