How to Use the COUNTIF Function with an Array Criteria in Excel

In the dataset below, we have Employee Names, Working Days and employee Status according to performance. We will apply the COUNTIF function with an array to count values.

Method 1 – Utilizing COUNTIF Function to an Array with OR Criteria in Excel

Steps:

Choose a cell (F6) and enter the below formula:

=COUNTIF(D5:D13,{"Excellent","Bad"})

Where,

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

Press ENTER.

You will get the cell count with your desired text values.

Method 2 – Applying the COUNTIF Function for an Array with Unique Values in Excel

2.1 COUNTIF Function for an Array with Unique Text Values

Steps:

Choose a cell (F6) and enter the following 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 from the COUNTIF It changes the unique values to 1 and the duplicates to 0.
The SUM function adds up all the counted unique values; thus, the output is 1.

Click the CTRL+SHIFT+ENTER key from the keyboard to apply an array.

You will get your desired text values counted in the chosen cell.

2.2 COUNTIF Function for an Array with Unique Numeric Values

Steps:

Select a cell (F5) and enter the following 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.