Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use COUNTIF Function with Array Criteria in Excel

While working in Microsoft Excel sometimes we need to use the COUNTIF function with array criteria. But 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 COUNTIF function with array criteria in Excel. Stay tuned!


Download Practice Workbook

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


3 Suitable Ways of Applying COUNTIF Function with Array Criteria in Excel

In the following, I have described 3 simple and quick methods of applying 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 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: COUNTIF Excel Example (22 Examples)


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: How to Use COUNTIF Function to Count Text from List in Excel


Similar Readings


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: COUNTIF vs COUNTIFS in Excel (4 Examples)


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 Use Excel COUNTIF That Does Not Contain Multiple Criteria


Conclusion

In this article, I have covered almost all the possible methods of 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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo