How to Use COUNTIF Function with Array Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel, sometimes we need to use the COUNTIF function with array criteria. However, it might seem difficult to you when applying an array formula with the COUNTIF function. Today in this article, I am sharing with you how to use the COUNTIF function with array criteria in Excel. Stay tuned!


How to Use COUNTIF Function with Array Criteria in Excel

In the following, I have described 3 simple and quick methods of applying the COUNTIF function with array criteria in Excel.

Let’s say, we have a dataset of some Employee Name, Working Days and their Status of working according to their performance. Now we will apply the COUNTIF function with array to count values with some simple tricks.


1. Utilizing COUNTIF Function to Array with OR Criteria in Excel

As we already know the COUNTIF function takes AND criteria automatically. But if you want to use the COUNTIF function with OR criteria, you have to use an array inside the formula. Follow the instruction below-

Steps:

  • First, choose a cell (F6) and write the below formula down-
=COUNTIF(D5:D13,{"Excellent","Bad"})

Where,

  • The COUNTIF function is counting cells with “Excellent” and “Bad” text values from the given range “D5:D13”.

Utilizing COUNTIF Function to Array with OR Criteria in Excel

  • Second, press ENTER.
  • Finally, you will get the count of cells with your desired text values within a moment. Simple isn’t it?

Utilizing COUNTIF Function to Array with OR Criteria in Excel

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


2.   Applying COUNTIF Function for Array with Unique Values in Excel

In some cases, you might need to count unique values from a complex dataset. Well, the COUNTIF function with array criteria is your savior in that situation. In the following, I have explained counting unique text and numeric values with a simple formula. Please check.


2.1 COUNTIF Function for Array with Unique Text Values

Here, we will count unique text values from our dataset.

Steps:

  • Similarly, choose a cell (F6) and apply the below formula-
=SUM(IF(COUNTIF($D$5:$D$13,$D$5:$D$13)=1,1,0))

Where,

  • The COUNTIF function will count the occurrences of values from the given range.
  • Then, the IF function constructs the results obtained from the COUNTIF It changes the unique values to 1 and the duplicates to 0.
  • Hence, the SUM function adds up all the counted unique values thus the output stands 1.

COUNTIF Function for Array with Unique Text Values

  • Next, click the CTRL+SHIFT+ENTER key from the keyboard to apply an array.
  • Within seconds, you will get your desired text values counted in the chosen cell.

Read More: COUNTIF Between Two Cell Values in Excel


2.2 COUNTIF Function for Array with Unique Numeric Values

Let’s count unique numeric values from the data table with the combination of the SUM, IF, ISNUMBER, and COUNTIF function.

Steps:

  • To start with, select a cell (F5) and put the below formula into the cell-
=SUM(IF(ISNUMBER($C$5:$C$13)*COUNTIF($C$5:$C$13,$C$5:$C$13)=1,1,0))

Where,

  • The COUNTIF function counts the number of occurrences from the selected range.
  • The ISNUMBER function returns TRUE if the provided value is a number otherwise it returns False.
  • The IF function converts the unique values to 1 and for duplicates 0.
  • The SUM function provides the final output summing up the total counted values.

COUNTIF Function for Array with Unique Numeric Values

  • In the same fashion, press CTRL+SHIFT+ENTER.
  • In summary, the counted unique values will be in your hands.

Read More: Excel COUNTIF to Count Cells Greater Than 1


3. Using COUNTIF Function for Array with Multiple Criteria in Excel

While working with a large set of data you might need to count cells with multiple criteria. In that case you can apply the COUNTIF function with an array to get your precious result.

Steps:

  • Simply, select a cell (F6) and write the below formula down-
=COUNTIF(D5:D13,"Excellent")+COUNTIF(D5:D13,"Bad")

Using COUNTIF Function for Array with Multiple Criteria in Excel

  • Gently, hit CTRL+SHIFT+ENTER to get the output.
  • In conclusion, we have successfully counted the cells with desired lookup values from the range.

excel countif array

Read More: How to Calculate Frequency Using COUNTIF Function in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have covered almost all the possible methods of the COUNTIF array formula in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


Related Articles


<< Go Back to Excel COUNTIF 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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo