COUNTIF With Multiple Criteria in Excel

What Is COUNTIF And COUNTIFS Function With Multiple Criteria?

The COUNTIF and COUNTIFS functions with Multiple Criteria checks the dataset based on the specified one or more condition, respectively, and returns the count of the cells that fulfill the criteria. The function helps us to check for Multiple Criteria in the same column or Multiple Criteria in multiple columns. The COUNTIF and COUNTIFS functions are inbuilt Statistical functions, so we can insert the formula from the “Function Library” or enter it directly in the worksheet.

For example, in the following table, column A contains some values. We will count the cells that fulfill the specified condition using the COUNTIFS formula.

Select cell B2, enter the formula =COUNTIFS(A2:A5,“AA”), and press “Enter”.

The output is shown above. The criteria is to find the count of cells with the value “AA”, within the cell range. Only one cell with that value matches the criteria, and so the count is 1.

What Is COUNTIF And COUNTIFS Function With Multiple Criteria?

Using the COUNTIF formula, users can count the cell matching the test condition or criteria. It is a Statistical Excel function.
The second argument of the COUNTIF function is ‘Criteria,’ which is case insensitive.
We can search the criteria for both numeric and alphabetic table arrays.
The COUNTIF and COUNTIFS functions are almost the same. However, we must add or repeat the formula for the COUNTIF with Multiple Criteria, but add the cell ranges and the required criteria within the formula, separated by a comma, for the COUNTIFS with Multiple Criteria.

How To Use COUNTIF With Multiple Criteria?

The steps to use the COUNTIF with Multiple Criteria are as follows;

Choose an empty cell for the output.
Type =COUNTIF( select the cell range, enter the first criteria as a cell value or a reference, and close the brackets.
Then press the “+”, and repeat step 2 with new values.
Then press the “+”, and repeat step 2 with new values.
The formula will be as per the syntax =COUNTIF(range,criteria)+COUNTIF(range,criteria), and finally, press “Enter”.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples Of COUNTIF With Multiple Criteria in Excel

We will understand some advanced scenarios with examples for the COUNTIF with Multiple Criteria.

Example #1

We will count the values using the COUNTIF with Multiple Criteria.

In the table, the data is,

Column A shows the values.
Column B will display the output.

COUNTIF with Multiple Criteria - Example 1

The steps to count the number of cells using the COUNTIF with Multiple Criteria in excel are as follows:

Step 1: Select cell B2, and enter the formula with the first cell range and the first criteria =COUNTIF(A2:A5,“A”)

Step 2: In continuation with the formula, press the + sign, as shown below.

Step 3: Now, enter the formula with the new cell range and the required criteria =COUNTIF(A2:A5,“B”). So the complete formula is =COUNTIF(A2:A5,”A”)+COUNTIF(A2:A5,”B”).

Step 4: Finally, press “Enter”.

We get the output shown above. The count is 2, i.e., 2 cells matching the multiple criteria values A and B.

Example #2

We will count the values using the COUNTIF with Multiple Criteria.

In the table, the data is,

Column A shows the values.
Column B will display the output.

COUNTIF with Multiple Criteria - Example 2

The steps to count the number of cells using the COUNTIF with Multiple Criteria are as follows:

Step 1: Select cell B2, and enter the formula =COUNTIF(A2:A6,”Apple”)+COUNTIF(A2:A6,”Banana”).

Step 2: Press “Enter”.

We get the output shown above. Here, there are 2 cells matching the 2 criteria, so the COUNTIF is 2.

Example #3

We will count the values using the COUNTIF with Multiple Criteria.

In the table, the data is,

Column A shows the values.
Column B will display the output.

COUNTIF with Multiple Criteria - Example 3

The steps to count the number of cells are as follows:

Step 1: Select cell B2, and enter the formula =COUNTIF(A2:A5,”>20″)+COUNTIF(A2:A5,”<20″).

Step 2: Press “Enter”.

We get the output shown above. Here, there are 3 cells matching the 2 criteria, so the COUNTIF is 3.

How To Use COUNTIFS Excel Function With Multiple Criteria?

The steps to use the COUNTIFS with Multiple Criteria are as follows:

1: Choose an empty cell for the output.

2: Type =COUNTIFS( and select the cell range and enter the first criteria, second cell range, next required criteria, and so on… as a cell value or a reference, and close the brackets.

3: The formula will be as per the syntax =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…), and finally, press “Enter”.