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.


Download Practice Workbook

You can download the practice workbook from here.


2 Suitable Examples of Excel COUNTIFS for Cells Not Equal to Multiple Text

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.

Read More: Advanced Use of COUNTIFS Function in Excel (9 Examples)


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.

Read More: How to Use COUNTIFS Function with 3 Criteria in Excel


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.

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


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. For more articles, stay connected with ExcelDemy. Lastly, if you have any questions, feel free to let me know in the comment section below.


Related Articles

Mashhura Jahan
Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo