Arin Islam
Looking for ways to know how to use the COUNTIFS function in Excel with multiple criteria in the same column? We can count values, texts or dates with multiple criteria in the same and different columns using the COUNTIFS function in Excel. Here, you will find 6 ways to use the COUNTIFS function in Excel with multiple criteria in the same column.
Download Practice Workbook
6 Ways to Use COUNTIFS Function in Excel with Multiple Criteria in Same Column
Here, we have a dataset containing Date, name of Sales Person, Product and Sales of a shop. Now, we will show you how to use the COUNTIFS function in Excel with multiple criteria in the same column using this dataset.
1. Use of COUNTIFS Function in Excel with Multiple Criteria in Same Column Based on Text Value
In the first method, we will count text values based on multiple criteria in the same column using the COUNTIFS function in Excel.
Now, from our dataset, we will count Shirt and Jacket text values in Column B using the COUNTIFS function in Excel.
Follow the steps given below to do it on your own.
Steps:
- Firstly, select Cell B14.
- Then, insert the following formula.
=COUNTIFS(B5:B11,{"Shirt","Jacket"})
Here, we set Cell range B5:B11 as criteria_range for both criteria and set “Shirt” and “Jacket” as criteria.
- After that, press ENTER to get the count value of Total Shirts and Jackets.
Finally, we will get both count values from the same column of Total Shirts in Cell B14 and Total Jackets in Cell C14. Thus, you can count text values based on multiple criteria in the same column using the COUNTIFS function in Excel.
Read More: COUNTIFS to Count Across Multiple Columns in Excel
2. Multiple Criteria in Same Column Based on Number Criteria
Now, you will find a way to count numbers based on multiple criteria in the same column using the COUNTIFS function in Excel.
Here, we will count the Sales values between $400 to $600 in Column C using the COUNTIFS function.
Here are the steps.
Steps:
- In the beginning, select Cell C13.
- After that, insert the following formula.
=COUNTIFS(C5:C11,">400",C5:C11,"<600")
In the COUNTIFS Function, we set Cell range C5:C11 as criteria_ range and set “>400” as the first criteria and “<600” as the second criteria.
- Then, press ENTER to get the count value of Sales between $400 to $600.
Finally, you will get count values from the same column of Sales between $400 to $600 in Cell C13. Thus, you can count number values based on multiple criteria in the same column using the COUNTIFS function in Excel.
Read More: Excel COUNTIFS Not Working with Multiple Criteria (2 Solutions)
3. Applying COUNTIFS Function with Multiple Criteria in Same Column Based on Dates
Next, you will find a way to count dates based on multiple criteria in the same column using the COUNTIFS function in Excel.
Here, we will count the Sales values between 1-Aug to 1-Oct using the Dates in Column B by applying the COUNTIFS function.
Go through the following steps given below to do it on your own dataset.
Steps:
- Firstly, select Cell C13.
- After that, insert the following formula.
=COUNTIFS(B5:B11,">8/1/2022",B5:B11,"<10/1/2022")
In the COUNTIFS Function, we set Cell range B5:B11 as criteria_ range and set “8/1/2022” as criteria1 and “10/1/2022” as criteria2.
- Then, press ENTER to get the count value of Sales between 1-Aug to 1-Oct.
Finally, you will get count values from the Sales between 1-Aug to 1-Oct in Cell C13 using Column B. Thus, you can count dates based on multiple criteria in the same column using the COUNTIFS function in Excel.
Read More: How to Use COUNTIFS with Date Range and Text in Excel
4. Using COUNTIFS and SUM Functions with Multiple Criteria in Same Column in Excel
In the fourth method, you will find a way to count text values based on multiple criteria in the same column using the COUNTIFS and SUM functions in Excel.
Let’s say, from our dataset we want to count the total value of the Shirt and Jacket in Column B using the COUNTIFS and SUM functions in Excel.
Go through the following steps given below to do that on your own.
Steps:
- In the beginning, select Cell C13.
- After that, insert the following formula.
=SUM(COUNTIFS(B5:B11,{"Shirt","Jacket"}))
Formula Breakdown
- Firstly, In the COUNTIFS function, we set Cell range B5:B11 as criteria_ range and set “Shirt” as criteria1 and “Jacket” as criteria2.
- Then, the SUM function will sum up the total output that we will get from the COUNTIFS Function.
- Now, press ENTER to get the total count value of the Shirt and Jacket.
Thus, you can count any value based on multiple criteria in the same column using the COUNTIFS and SUM functions in Excel.
5. Employing COUNTIFS and SUMPRODUCT Functions in Excel
We can also use the COUNTIFS and SUMPRODUCT functions to count values based on multiple criteria in the same column.
Now, from our dataset, we will count the total value of the Shirt and Jacket in Column B using the COUNTIFS and SUMPRODUCT functions in Excel.
Follow the steps given below to do it on your dataset.
Steps:
- Firstly, select Cell C13.
- After that, insert the following formula.
=SUMPRODUCT(COUNTIFS(B5:B11,{"Shirt","Jacket"}))
Formula Breakdown
- Firstly, In the COUNTIFS function, we set Cell range B5:B11 as criteria_ range and set “Shirt” as criteria1 and “Jacket” as criteria2.
- Then, the SUMPRODUCT function will sum up the total output that we will get from the COUNTIFS Function.
- Next, press ENTER.
Thus, you can count any value based on multiple criteria in the same column using the COUNTIFS and SUMPRODUCT functions in Excel.
6. Using OR Logic with Multiple Criteria in Same Column
The COUNTIFS function works with the AND Logic by default in all cases. But, we can also use it with OR Logic.
Here, with the dataset, we will show you how you can count Shirts or Jackets in Excel using the COUNTIFS function in Column B.
Here are the steps.
Steps:
- Firstly, select Cell C13.
- Then, insert the following formula.
=COUNTIFS(B5:B11,B5)+COUNTIFS(B5:B11,B6)
Here, in the first COUNTIFS function, we set Cell range B5:B11 as criteria_range and Cell B5 as criteria1 and in the second COUNTIFS function, we set the same Cell range as criteria_range and Cell B6 as criteria1. Then, we added the values to get the count value of Shirts or Jackets.
- After that, press ENTER.
Thus, you can use the COUNTIFS function with multiple criteria in the same column with OR Logic.
Read More: Excel COUNTIFS with Multiple Criteria and OR Logic (3 Examples)
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Conclusion
So, in this article, you will find 6 ways to use the COUNTIFS function in Excel with multiple criteria in the same column. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!
