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.

- We can use the excel function
**COUNTIF**to create a formula to implement exact and case insensitive compare to text value. - The function
**COUNTIF(range, criteria)Â**contains 2 parameters, the first one is the cells range, then second one is the text compare criteria. - The function
**COUNTIF**is**case insensitive**by default. - 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 - We input the formula
**=COUNTIF(A2:A6, C2)**in cell**D2**. - Then when we input a name text such as
**jerry Li**in cell**C2**, it will display number**2**in cell**D2**. - This is because there are
**2**cells contains text**jerry li**which use different character cases. - This means the comparation is case insensitive.
- When we input a name text
**jerry**in cell**C2**, it will display number**0**in cell**D2**. - This means the comparation is an exactly compare.

##### 1.1.2 Partially Equal To.

- We use the same data cells example as section
**1.1.1**. - 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. - We input the same formulaÂ
**=COUNTIF(A2:A6, C2)**in cell**D2**. - And input the the name text
***SUN**Â in cell**C2**, then it will display number**1**in cell**D2**. - This is because the cell
**A5**contains the text**Tom Sun**. - That means the comparation is case insensitive and partially.

#### 1.2 Case Sensitive Compare.

##### 1.2.1 Exactly Equal To.

- We use the same data cells example in section
**1.1.1**. - We use the excel function
**SUMPRODUCT**and**EXACT(value, range)**together to write the below formula. - Input the formula
**=SUMPRODUCT(–EXACT(C3,A2:A6))**in cell**D3**. - Then when you input text
**jerry li**in cell**C3**, it will display number 1 in cell**D3**. - When you input text
**jerry Li**in cell**C3**,Â it will display number 0 in cell**D3**. - 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.

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

##### 2.1.2 Partially Not Equal To.

- Input the text
***MA***in cell**C4**. - When you press the enter key, it will show the number
**4**in cell**D4**. - 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.

- You can use the excel function
**COUNTIF**to count all cells except a certain value in excel. - You can use the
**“<>”**operator to exclude the certain text value. - You can see
**section 2.1.1**for the example.

#### 3.2 Case Sensitive Compare.

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