How to Use COUNTIFS for Cells Not Equal to Multiple Text in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

COUNTIFS Not Equal to Multiple Text


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.

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

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.

Here, in the 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.

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

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.

Now, in the 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"))

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

  • 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"))

Utilize SUMPRODUCT Function to Count Cells for Criteria from Multiple Columns

  • 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.

Practice Sheet for COUNTIFS Not Equal to Multiple Text


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo