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.
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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
