# 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. 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!

## How to Apply 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”. • Second, press ENTER.
• Finally, you will get the count of cells with your desired text values within a moment. Simple isn’t it? ### 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. • 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. #### 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. • In the same fashion, press CTRL+SHIFT+ENTER.
• In summary, the counted unique values will be in your hands. ### 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")` • Gently, hit CTRL+SHIFT+ENTER to get the output.
• In conclusion, we have successfully counted the cells with desired lookup values from the range. ## 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. Stay tuned and keep learning.

## Related Articles 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 Advanced Excel Exercises with Solutions PDF  