How to countif with multiple criteria in Excel?

In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex, today, I will talk about some items for counting with multiple criteria.

MethodA: Countif with multiple criteria in same column

• Count cells with multiple criteria based on text value
• Count cells with multiple criteria between two values
• Count cells with multiple criteria between two dates

MethodB: Countif with multiple criteria in same column with a useful feature

MethodC: Countif with multiple criteria in multiple columns

More tutorials about countif cells ...

 

Countif with multiple criteria in same column

Count cells with multiple criteria based on text value

For example, I have the following data which contains some products, and now I need to count the number of KTE and KTO which are populated in the same column, see screenshot:

To get the number of KTE and KTO, please enter this formula:

And then press Enter key to get the number of these two products. See screenshot:

Notes:

1. In the above formula: A2:A15 is the data range that you want to use, KTE and KTO are the criteria that you want to count.

2. If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…

Count cells with multiple criteria between two values

If you need to count the number of cells that the value is between two given numbers, how to solve this job in Excel?

Take the below screenshot as an example, I want to get the result of the number that is between 200 and 500. Please do with these formulas:

Enter this formula into a blank cell where you want to locate the result:

And then press Enter key to get the result as you need, see screenshot:

Note: In the above formula:

• B2:B15 is the cell range that you want to use, >200 and >500 are the criteria you want to count the cells;
• the whole formula means, to find the number of cells that have a value greater than 200 and then subtract the count of cells with a value greater than 500.

Count cells with multiple criteria between two dates

To count the cells based on date range, the COUNTIF and COUNTIFS functions can do you a favor as well.

For example, I want to count the cell numbers that the date is between 5/1/2019 and 8/1/2019 in a column, please do as this:

Enter the below formula into a blank cell:

And then press Enter key to get the count number, see screenshot:

Note: In the above formula:

• B2:B15 is the cell range that you want to use;
• >=5/1/2018 and <=8/1/2019 are the date criteria you want to count the cells;

Click to know more about the COUNTIF function...

Countif with multiple criteria in same column with a useful feature

If you have Kutools for Excel, with its Select Specific Cells feature, you can quickly select the cells with specific text or cells between two numbers or dates, and then get the number you need.

After installing Kutools for Excel, please do as this:

1. Select the list of cells that you want to count cells based on criteria, and then click Kutools > Select > Select Specific Cells, see screenshot:

2. In the Select Specific Cells dialog box, please set the operations as you need, and then click OK, the specific cells have been selected and the number of the cells are shown in the prompt box as below screenshots shown:

Note: This feature also can help you to select and count the cells between two specific numbers or dates as the following screenshots shown:

Download and free trial Kutools for Excel Now!

Countif with multiple criteria in multiple columns

If there are multiple criteria in multiple columns, such as following screenshot shown, and I want to get the number of KTE whose order is greater than 300 and the name is Ruby.

Please type this formula into the desired cell:

and then press Enter key to get the number of KTE which you need.

Notes:

1. A2:A15 and KTE are the first range and criterion you need, B2:B15 and >300 are the second range and criterion you need, and the C2:C15 and Ruby are the third range and criterion you based on.

2. If there are more criteria you need based on, you just need to add the range and criteria within the formula, such as: =COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, range4, criteria4, … )

Click to know more about the COUNTIFS function...

More relative count cells articles: