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-
- First, choose a cell (F6) and write the below formula down-
- 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: 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.
- Similarly, choose a cell (F6) and apply the below formula-
- 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.
- Excel COUNTIF to Count Cell That Contains Text from Another Cell
- How to Compare Two Columns Using COUNTIF Function (4 Ways)
- VBA COUNTIF Function in Excel (6 Examples)
- How to Use COUNTIF Between Two Numbers (4 Methods)
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
2.2 COUNTIF Function for Array with Unique Numeric Values
- To start with, select a cell (F5) and put the below formula into the cell-
- 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: 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.
- Simply, select a cell (F6) and write the below formula down-
- Gently, hit CTRL+SHIFT+ENTER to get the output.
- In conclusion, we have successfully counted the cells with desired lookup values from the range.
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.
- How to Use the Combination of COUNTIF and SUMIF in Excel
- Use COUNTIF for Non Contiguous Range in Excel
- How to Use COUNTIF Function In Excel to Count Bold Cells
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Use IF and COUNTIF Functions Together in Excel
- Use COUNTIF and COUNTA Functions Together in Excel
- Excel COUNTIF Function with Conditional Formatting (7 Examples)