Sometimes you may need to use **the COUNTIF function** between** two **values with multiple criteria. So, if you are looking for the use of a **COUNTIF** function between** two **values with multiple criteria then you have come to the right place. In this article, I will demonstrate how to use the **COUNTIF** function between** two **values with multiple criteria in Excel.

**Table of Contents**Expand

## How to Use COUNTIF Function Between Two Values with Multiple Criteria: 6 Simple Methods

Here, I will describe **6 **suitable examples of how to use a **COUNTIF** function between** two **values with multiple criteria using the **Microsoft 365 version**. In addition, for your better understanding, I’m going to use a sample dataset. Moreover, the sample dataset has** 4 columns**. These are** States, Product, Quantity, **and **Sales**. The dataset is given below.

### 1. Use of COUNTIF Function for Same Column with Multiple Criteria

Here, I will use **multiple criteria in the COUNTIF function within the same column in Excel**. Suppose I want to find out how many orders have products such as** Shirts or T-shirts**. The steps are given below.

**📌 Steps:**

- Firstly, you have to select a new cell
**E16**where you want to keep the result. - Secondly, you should use the formula given below in the
**E16**cell.

`=COUNTIF(C5:C14,"Shirt")+COUNTIF(C5:C14,"T-shirt")`

**Formula Breakdown**

Here, the** COUNTIF **function will count those cells which will fulfill the given condition.

- Firstly,
**COUNTIF(C5:C14,”Shirt”)—>****C5:C14**is the criteria range and**“Shirt”**is the criteria. This means the**COUNTIF**function will return the number of cells that contain**Shirt**as their cell value.*Here, the***Inverted Comma**is a must for any written condition.**Output: 3.**

- Similarly,
**COUNTIF(C5:C14,”T-shirt”)—>****C5:C14**is the criteria range and**“T-shirt”**is the criteria. This means the**COUNTIF**function will return the number of cells that contain**T-shirt**as their cell value.*Here, the***Inverted Comma**is a must for any written condition.**Output: 4.**

- Finally, the
**Plus sign (+)**will add them.**Output: 7.**

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many orders have products as** Shirts or T-shirts**.

### 2. Employing COUNTIF Between Two Values with Multiple Criteria

Now, I will use multiple criteria in the **COUNTIF** function between **two** values. Suppose, I want to find out how many orders have a selling quantity of **more than** **300** but** less than or equal to 600**. The steps are given below.

**📌 Steps:**

- Firstly, you have to select a new cell
**H10**where you want to keep the result. - Secondly, you should use the formula given below in the
**H10**cell.

`=COUNTIF(D5:D14,">300")-COUNTIF(D5:D14,">600")`

**Formula Breakdown**

Here, the** COUNTIF **function will count those cells which will fulfill the given condition.

- Firstly,
**COUNTIF(D5:D14,”>300″)—> D5:D14**is the criteria range and**“>300”**is the criteria. This means the**COUNTIF**function will return the number of cells that contain a cell value of**greater than 300**.*Here, the***Inverted Comma**is a must for any written condition.**Output: 8.**

- Again,
**COUNTIF(D5:D14,”>600″)—> D5:D14**is the criteria range and**“>600”**is the criteria. This means the**COUNTIF**function will return the number of cells that contain a cell value of**greater than 600**. As with the**1st COUNTIF**function, I got the cells that are also greater than**600**so I must find the number which is greater than only**600,**and then I will**subtract**it.**Output: 2.**

- Finally, the
**Minus sign (-)**will do the operation of subtraction.**Output: 6.**

- Lastly, press
**ENTER**to get the result.

As a result, you will get how many orders have a selling quantity of **more than** **300** but** less than or equal to 600**.

**Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel**

### 3. Applying SUMPRODUCT & COUNTIF Functions within Same Range

Let’s try another way to use the **COUNTIF** function between **two **values with multiple criteria. Here, I want to find out how many orders have products such as** Shirts or T-shirts**. Moreover, you can get a similar result to method 1 by applying **the SUMPRODUCT function**, too.

Now, let’s see the steps.

**📌**** Steps:**

- Firstly, select Cell
**G11**& type-

`=SUMPRODUCT(COUNTIF(C5:C14,{"Shirt";"T-shirt"}))`

**Formula Breakdown**

- Here, the
**COUNTIF**function will count cells from the**C5:C14**data range, which will fulfill the given condition. Additionally,**{“Shirt”;”T-shirt”}**is the criteria range. Furthermore, the**second bracket**is for the array.**Output: {3;4}.**

- Then, the
**SUMPRODUCT**function will add them.**Output: 7.**

- Subsequently, press
**ENTER**to get the result.

Finally, you will get the **total counts** for both criteria.

**Read More: ****SUMPRODUCT and COUNTIF Functions with Multiple Criteria**

### 4. Use of COUNTIFS Function with Multiple Columns

Here, I will use multiple criteria in **the** **COUNTIFS function** across different columns. Suppose I want to find out how many orders are from **California** and **T-shirts** as the product. The steps are given below.

**📌 Steps:**

- Firstly, you have to select a new cell
**H10**where you want to keep the result. - Secondly, you should use the formula given below in the
**H10**cell.

`=COUNTIFS(B5:B14,H7,C5:C14,H8)`

**Formula Breakdown**

Here, the** COUNTIFS **function will count those cells which will fulfill the given conditions.

- Firstly,
**B5:B14**is the**1st**criteria range and**H7**is the criteria. - Secondly,
**C5:C14**is the**2nd**criteria range and**H8**is the criteria. - Finally, the
**COUNTIFS**function will count those cells which fulfill both criteria.**Output: 2.**

- Lastly, press
**ENTER**to get the result.

As a result, you will get how many orders are from **California** and **T-shirts** as the product.

**Read More: ****COUNTIF with Multiple Criteria in Different Columns in Excel**

### 5. Using Combined Functions with Multiple Ranges & Criteria

You can apply the **Wildcard** characters in the **COUNTIF** function with multiple criteria within different ranges. Now, let’s find out how many cells have the right information. The steps are given below.

**📌 Steps:**

- First, you have to select a new cell
**H11**where you want to keep the result. - Then, you should use the formula given below in the
**H11**cell.

`=COUNTIF(B5:C14,"*")+COUNT(D5:D14)+SUMPRODUCT(--ISLOGICAL(E5:E14))`

**Formula Breakdown**

- Firstly,
**COUNTIF(B5:C14,”*”)—> B5:C14**is the criteria range. And**“*” (Asterisk)**is the particular criteria. Which denotes any cell with**text**value. So, the**COUNTIF**function will search for those cells which contain**text**as cell value.**Output: 10.**

- Secondly,
**COUNT(D5:D14)—>**here,**the****COUNT function**will return the total cell number which contains any number as a cell value.**Output: 6.**

- Thirdly,
**ISLOGICAL(E5:E14)—>**here,**the ISLOGICAL function**will check if the cell contains any logical value**(TRUE/FALSE)**or not.**Output: {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}.**

- Fourthly, the
**Double Hyphen (–)**will convert**TRUE to 1**and**FALSE to 0.****Output: {1;0;1;0;0;1;0;1;0;0}.**

- Then, the
**SUMPRODUCT**function will add them.**Output: 4.**

- Finally, the
**Plus sign (+)**will add them.**Output: 20.**

- Subsequently, press
**ENTER**and you will get the result.

### 6. COUNTIF Between Dates with Multiple Criteria

Here, I will use multiple criteria in the **COUNTIF** function between **two **values. Suppose, I want to find out how many orders have sold after **5/25/2022** and before **7/25/2022.** The steps are given below.

**📌 Steps:**

- Firstly, you have to select a new cell
**H10**where you want to keep the result. - Secondly, you should use the formula given below in the
**H10**cell.

`=COUNTIF(E5:E14,">5/25/2022")-COUNTIF(E5:E14,">=7/25/2022")`

**Formula Breakdown**

Here, the** COUNTIF **function will count those cells which will fulfill the given condition.

- Firstly,
**COUNTIF(E5:E14,”>5/25/2022″)—> E5:E14**is the criteria range and**“>5/25/2022”**is the criteria. This means the**COUNTIF**function will return the number of cells that contain a cell value**greater than 5/25/2022**.*Here, the***Inverted Comma**is a must for any written condition.**Output: 6.**

- Again,
**COUNTIF(E5:E14,”>=7/25/2022″)—> E5:E14**is the criteria range and I have to use**“>=7/25/2022”**as the criteria. This means the**COUNTIF**function will return the number of cells that contain a cell value**greater than or equal to 7/25/2022**. As with the**1st COUNTIF**function, I got the cells that are also greater than**7/25/2022**so I must find the number which is greater than only**7/25/2022,**and then I will**subtract**it. Furthermore, I should not count this (**7/25/2022**) cell value too.**Output: 3.**

- Finally, the
**Minus sign (-)**will do the operation of subtraction.**Output: 3.**

- Lastly, press
**ENTER**to get the result.

As a result, you will get how many orders have sold after **5/25/2022** and before **7/25/2022.**

## How to Use INDEX-MATCH Formula with COUNTIF Function for Multiple Criteria in Excel

In this section, I will combine the **COUNTIF**, **INDEX**, **SUM**, and **MATCH** functions of Excel to count based on multiple criteria. Now, see the below-given steps for a better understanding.

- Firstly, select Cell
**H11**& type-

`=SUM(COUNTIF(INDEX(B4:E14,,MATCH(B5,B4:B14,0)),{"T-shirt";"Jacket"}))`

**Formula Breakdown**

- Firstly,
**MATCH(B5,B4:B14,0)—>**the**MATCH**function uses the cell value of**B5**as the lookup value, then**B4:B14**for the array, and**0**for an**exact**match.**Output: 2.**

- Secondly,
**INDEX(B4:E14,,2)—>**the**INDEX**function will return the entire column of**Product**, which is**C4:C14**in the above image. - Thirdly, the
**COUNTIF**function will search for values**{“T-shirt”;”Jacket”}**. - Finally, the
**SUM**function will add all the cells.

- Lastly, press
**ENTER**to get the result.

As a result, you will get the total count for **T-shirt **and **Jacket**.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained **6 **suitable examples to use the **COUNTIF** function between two values with multiple criteria. Please drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**How to Use COUNTIF Between Two Dates and Matching Criteria in Excel****Excel COUNTIF Function with Multiple Criteria & Date Range****How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel****How to Use COUNTIF Function Across Multiple Sheets in Excel**

**How to Apply COUNTIF with Multiple OR Criteria in Excel**

**<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel**