## 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 **

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

**=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,

*and*

**KTE***are the criteria that you want to count.*

**KTO**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 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:

**=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:

is the cell range that you want to use,**B2:B15**and**>200**are the criteria you want to count the cells;**>500**- 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 then press**Enter**key to get the result. - 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 5/1/2019 and 8/1/2019 in a column, please do as this:

Enter the below formula into a blank cell:

**=COUNTIFS($B$2:$B$15, ">=5/1/2019", $B$2:$B$15, "<=8/1/2019")**

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

**Note**: In the above formula:

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

**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.

**Tips:**To apply this **Select Specific Cells** feature, firstly, you should download the **Kutools for Excel**, and then apply the feature quickly and easily.

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:

**=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

*are the first range and criterion you need,*

**KTE***and*

**B2:B15***are the second range and criterion you need, and the*

**>300***and*

**C2:C15***are the third range and criterion you based on.*

**Ruby**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 then press**Enter**key to get the result.

**Click to know more about the COUNTIFS function...**

** More relative count cells articles:**

**Countif To Calculate The Percentage In Excel**- For example, I have a summary report of a research paper, and there are three options A, B, C, now I want to calculate the percentage of each these three options. That is to say, I need to know the option A accounts for the percentage of all options.

**Countif A Specific Value Across Multiple Worksheets**- Supposing, I have multiple worksheets which contain the following data, and now, I want to get the number of occurrence of a specific value “Excel” from theses worksheets. How could I count specific values across multiple worksheet?

**Countif Partial String/Substring Match In Excel**- It’s easy to countif cells filled with certain strings, but do you know how to countif cells containing only partial string or substrings in Excel? This article will introduce a couple of methods to solve it quickly.

**Count All Cells Except A Specific Value In Excel**- If you have the word "Apple" scattered among a list of values, now, you just want to count the number of cells which are not "Apple" to get the following result. In this article, I will introduce some methods to solve this task in Excel.

**Count Cells If One Of Multiple Criteria Met In Excel**- The COUNTIF function will help us count cells containing one criterion, and the COUNTIFS function can help up count cells containing a set of conditions or criteria in Excel. What if counting cells if contains one of multiple criteria? Here I will share the ways to count cells if contain X or Y or Z … etc. in Excel.

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!