The COUNTIFS function gives you the opportunity to provide more than one criterion and returns the count of cells that meet all the criteria at the same time. You can also provide not equal criteria in the COUNTIFS function. The focus of this article is to explain how to use COUNTIFS for cells not equal to multiple text in Excel. So, without any delay, let’s start the discussion.

**Table of Contents**Expand

## How to Use COUNTIFS for Cells Not Equal to Multiple Text in Excel: 2 Suitable Examples

Here, I have taken the following dataset to explain this article. It contains 3 columns, State, Product, and Sales. Now, I will calculate the No. of Sales for not equal to multiple text using **the COUNTIFS function** in Excel. I will explain 2 suitable examples.

### 1. Apply COUNTIFS Function to Count If Not Equal to Multiple Text from Single Column

In this first example, I will count the number of cells if not equal to multiple text from a single column using the** COUNTIFS** function in Excel. In the following image, you can see that the criteria are Product is not equal to the Router and not equal to the Headphone. I will calculate the No. of Sales here.

Let’s see how you can do it.

**Steps:**

- Firstly, select the cell where you want to calculate the No. of sales. Here, I selected Cell
**C20**. - Secondly, in Cell
**C20**write the following formula.

`=COUNTIFS(C5:C14,"<>Router",C5:C14,"<>Headphone")`

- Thirdly, press
**Enter**to get the result.

**COUNTIFS**function, I selected cell range

**C5:C14**as

**criteria_range1**,

**“<>Router”**as

**criteria_1**,

**C5:C14**as

**criteria_range2**, and

**“<>Headphone”**as

**criteria2**. The formula will return the number of cells that match both of the criteria.

### 2. Employ COUNTIFS Function to Count If Not Equal to Text from Multiple Columns

Now, I will employ the **COUNTIFS** function to count the number of cells if not equal to multiple text from multiple columns in Excel. Here, you can see that the Criteria are State not Equal to Texas and Product not equal to Router. I will calculate the No. of Sales.

Let’s see the steps.

**Steps:**

- In the beginning, select the cell where you want to calculate the No. of Sales.
- Next, write the following formula in that selected cell.

`=COUNTIFS(C5:C14,"<>Router",B5:B14,"<>Texas")`

- After that, press
**Enter**and you will get the result.

**COUNTIFS**function, I selected cell range

**C5:C14**as

**criteria_range1**,

**“<>Router”**as

**criteria_1**,

**B5:B14**as

**criteria_range2**, and

**“<>Texas”**as

**criteria2**. The formula will return the number of cells that match both of the criteria.

## Alternative Solution to Count Cells If Not Equal to Multiple Text in Excel

In this section, I will show you an alternative solution for counting the number of cells in this type of situation. Here, I will use **the SUMPRODUCT function** to count if not equal to multiple text in Excel. I will use the same dataset I used before and explain the same examples with a different formula. Let’s explore the solutions.

### 1. Insert SUMPRODUCT Function to Count Cells for Criteria from a Single Column

Here, I will use the **SUMPRODUCT** function to count the No. of Sales if not equal to multiple cells from the same column. Let me show you how to do it.

**Steps:**

- Firstly, select the cell where you want to count the No. of Sales.
- Secondly, write the following formula in that selected cell.

`=SUMPRODUCT((C5:C14<>"Router")*(C5:C14<>"Headphone"))`

- Thirdly, press
**Enter**and you will get the No. of Sales. - Furthermore, if you are using an older version of
*Microsoft Excel*than**Excel 2019**then press**Ctrl + Shift + Enter**.

**🔎** **How Does the Formula Work?**

**(C5:C14<>”Router”):**Here, the formula returns an**array**containing**TRUE**and**FALSE**. It returns**TRUE**if the criteria meet. Otherwise, returns**FALSE**.**(C5:C14<>”Headphone”):**Now, the formula returns an**array**containing**TRUE**and**FALSE**. It returns**TRUE**if the criteria meet. Otherwise, returns**FALSE**.**SUMPRODUCT((C5:C14<>”Router”)*(C5:C14<>”Headphone”)):**Finally, the**SUMPRODUCT**function multiplies these 2 arrays and then returns the summation of the results.

### 2. Utilize SUMPRODUCT Function to Count Cells for Criteria from Multiple Columns

In this example, I will utilize the **SUMPRODUCT **function to count cells for criteria from multiple columns. Let’s see the steps.

**Steps:**

- In the beginning, select the cell where you want to calculate the No. of Sales. Here, I selected Cell
**C20**. - In the following step, in Cell
**C20**write the following formula.

`=SUMPRODUCT((B5:B14<>"Texas")*(C5:C14<>"Router"))`

- Finally, press
**Enter**to get the result. If you are using an older version of*Microsoft Excel*than**Excel****2019**then press**Ctrl + Shift + Enter**.

**🔎** **How Does the Formula Work?**

**(B5:B14<>”Texas”):**Here, the formula returns an**array**containing**TRUE**and**FALSE**. It returns**TRUE**if the criteria meet. Otherwise, returns**FALSE**.**(C5:C14<>”Router”):**Now, the formula returns an**array**containing**TRUE**and**FALSE**. It returns**TRUE**if the criteria meet. Otherwise, returns**FALSE**.**SUMPRODUCT((B5:B14<>”Texas”)*(C5:C14<>”Router”)):**Finally, the**SUMPRODUCT**function multiplies these 2 arrays and then returns the summation of the results.

**Read More: **Advanced Use of COUNTIFS Function in Excel

## Things to Remember

- If you are using an older version of
*Microsoft Excel*than**Excel 2019**then you must press**Ctrl + Shift + Enter**for the array formula.

## Practice Section

Here, I have provided a practice sheet for you to practice how to use **COUNTIFS** for cells if not equal to multiple text in Excel.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

To conclude, I tried to explain how to use **COUNTIFS **for cells if not equal to multiple text in Excel. I explained 2 different examples. Here, I also explained an alternative solution using the **SUMPRODUCT** function for the same situation. Moreover, I have also added the practice workbook at the beginning of the article. So, you can download it to practice. I hope this article was helpful to you. If you have any questions, feel free to let me know in the comment section below.

## Related Articles

**<< Go Back to Excel COUNTIFS Function | Excel Functions | Learn Excel**