Wasim Akram
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?
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.
- 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
- Excel COUNTIF to Count Cell That Contains Text from Another Cell
- How to Compare Two Columns Using COUNTIF Function
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today 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.
- In the same fashion, press CTRL+SHIFT+ENTER.
- In summary, the counted unique values will be in your hands.
Read More: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
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.
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 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
- Excel COUNTIF to Count Cells Greater Than 1
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use COUNTIF Function In Excel to Count Bold Cells
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Use the Combination of COUNTIF and SUMIF in Excel
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Use IF and COUNTIF Functions Together in Excel
- How to Use Nested COUNTIF Function in Excel
- How to Use COUNTIF and COUNTA Functions Together in Excel
- Excel COUNTIF Function with Conditional Formatting
- [Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value
- [Fixed] COUNTIF Function with Wildcard Not Working in Excel
- [Fixed!] Excel COUNTIF Function Not Working for String “True”
