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.

Method1: 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

Method2: Countif with multiple criteria in multiple columns

 

Combine multiple worksheets, workbooks and csv files into one worksheet / workbook:

In your daily work, to combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks into one worksheet or workbook.

Download and free trial 60-day

 

Countif with multiple criteria in same column

Layoff season is coming, still work slowly?
-- Office Tab boosts your pace, saves 50% work time!

•  Amazing! The operation of multiple documents is even more relaxing and convenient than single document;
•  Compared with other web browsers, the interface of Office Tab is more powerful and aesthetic;
•  Reduce thousands of tedious mouse clicks, say goodbye to cervical spondylosis and mouse hand;
•  Be chosen by 90,000 elites and 300+ well-known companies!

Full feature, Free Trial 30-day          Read More             Download Now!

 

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:

=COUNTIF($A$2:$A$15,"KTE")+COUNTIF($A$2:$A$15,"KTO"),  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)+…

Tips: Another compact formula also can help you to solve this problem: =SUMPRODUCT(COUNTIF($A$2:$A$15,{"KTE";"KTO"})), and then press Enter key to get the result. And you can add the criteria just as =SUMPRODUCT(COUNTIF(range,{ "criteria1";"criteria2";"criteria3";"criteria4"…})).

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 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: =COUNTIF($B$2:$B$15,">200")-COUNTIF($B$2:$B$15,">500"),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.

Tips: You can also apply the COUNTIFS function to deal with this task, please type this formula: =COUNTIFS($B$2:$B$15,">200",$B$2:$B$15,"<500").And you can add the criteria just as =COUNTIFS(range1,"criteria1",range2,"criteria2",range3,"criteria3",...).

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 11/30/2018 and 2/20/2019 in a column, please do as this:

Enter this formula: =COUNTIF($B$2:$B$15, ">11/30/2018") - COUNTIF($B$2:$B$15, ">2/20/2019") into a blank cell to output the result, 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;
• >11/30/2018 and >2/20/2019 are the date criteria you want to count the cells;
• the whole formula means, to find the number of cells that date greater than 11/30/2018 and then subtract the count of cells with a date greater than 2/20/2019.

Tips: You can also apply the COUNTIFS function to deal with this task, please type this formula: =COUNTIFS($B$2:$B$15, ">11/30/2018", $B$2:$B$15, "<2/20/2019").

Countif with multiple criteria in multiple columns with formula

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 200 and the name is Ruby.

Please type this formula into a desired cell: =COUNTIFS($A$2:$A$15,"KTE",$B$2:$B$15,">300",$C$2:$C$15,"Ruby"), 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,… )

Tips: Here is another formula also can help you: =SUMPRODUCT(--($A$2:$A$15="KTE"),--($B$2:$B$15>300),--($C$2:$C$15="Ruby")), and press Enter key to return the result.

Related articles:

How to use countif to calculate the percentage in Excel?

How to countif a specific value across multiple worksheet?

• Kutools for Excel
• Combine multiple worksheets
• Count or sum by color
• Send multiple emails

Kutools for Excel

Kutools for Excel: 300+ handy features for Excel of free trial

Collecting more than 300 smart and professional tools for Excel, it simplifies various kinds of complicated tasks into a few clicks in your daily work, which can save much time and increase productivity.

• Combine multiple worksheet or workbooks into single workbook or worksheet
• Count and sum cells based on background, font or conditional formatting color
• Send the same email to a list of recipients individually with different attachments
• 110,000+ Excel users' choice. Full feature free trial 60-day, no credit card required!
• Full money back within 60 days without any reason!

Read More Download Now! Purchase Now!

Combine multiple worksheets

Combine multiple worksheets or workbooks into one single sheet or workbook

In your daily work, merging multiple worksheets or workbooks into one sheet or workbook may be a huge job for you to combine them one by one. But, with Kutools for Excel's Combine feature, you can solve this task with only several clicks.

Read More Download Now! Purchase Now!

Count or sum by color

Count and sum cells based on background, font or conditional formatting color

It may be a difficult task for you to count or sum the cell values based on background, font or conditional formatting color in a large range, Kutools for Excel supports an easy tool-Count by Color which can help you to deal with this job as quickly as possible without any Excel skills.

Read More Download Now! Purchase Now!

Send multiple emails

Send the same email to a list of recipients individually with different attachments

You can apply the Word application to send personalized emails to multiple persons without attachments, but, With Kutools for Excel's Send Emails feature, this limatation can be remedied. At the same time, you can CC or Bcc the messages to a specific person as well.

Read More Download Now! Purchase Now!