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

- 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 containing text**jerry li**which use different character cases. - This means the comparison is case-insensitive.
- When we input a name text
**jerry**in cell**C2**, it will display the number**0**in cell**D2**. - This means the comparison is exact.

##### 1.1.2 Partially Equal To.

- We use the same data cell 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**. - Input the name text
***SUN**Â in cell**C2**, then it will display the number**1**in cell**D2**. - This is because cell
**A5**contains the text**Tom Sun**. - That means the comparison 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 functions
**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 the number 0 in cell**D3**. - 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.

- 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 contain the text
**jerry Li**and 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 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.

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

#### 3.2 Case Sensitive Compare.

- You can use the
**EXACT**function to get the cell number that contains a certain value. - Then use the total cell number minus the above number to get the cell number that does 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 contains the text**jerry li**.