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 compare to text value.
2. The function COUNTIF(range, criteria) contains 2 parameters, the first one is the cells range, then second one is the text compare criteria.
3. The function COUNTIF is case insensitive by default.
4. Below is the example data cells in this article. Column A‘s cells contains all the user name text.
   

   	A	B	C	D
   1	User Name		Name	Count
   2	Jerry Li		jerry Li	2
   3	jerry li
   4	Tom Ma
   5	Tom Sun
   6	Jackie Liu

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 contains text jerry li which use different character cases.
8. This means the comparation is case insensitive.
9. When we input a name text jerry in cell C2, it will display number 0 in cell D2.
10. This means the comparation is an exactly compare.

1.1.2 Partially Equal To.

1. We use the same data cells 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. And input the the name text *SUN in cell C2, then it will display number 1 in cell D2.
5. This is because the cell A5 contains the text Tom Sun.
6. That means the comparation 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 function 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 number 0 in cell D3.
6. This means the comparation is case sensitive and exactly.

2.  How To Count Cells That 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 contains the text jerry Li 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 which 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 the certain text value.
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 cells number which contains the certain value.
2. Then use the total cells number to minus the above number to get the cells number that 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 contain the text jerry li.