Musiha Mahfuza Mukta
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.
Download Practice Workbook
You can download the practice workbook from here:
6 Methods to Use COUNTIF Function Between Two Values with Multiple Criteria
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.
Read More: COUNTIF with Multiple Criteria in Different Columns in Excel
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 COUNTIF Not Equal to Text or Blank 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: Excel COUNTIF for Multiple Criteria with Different Column
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.
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
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.
Read More: How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
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.
Read More: INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
Practice Section
Now, you can practice the explained method by yourself.
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. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
