While working with a large Microsoft Excel, sometimes we need to count cells with multiple criteria. In our Excel worksheet for the convenience of our work, we keep some cells blank. That’s why we count those cells which are not blank. In this article, we’ll learn five quick and suitable ways to countif multiple criteria same column in Excel effectively with appropriate illustrations.
Let’s say, we have a dataset that contains information about several Projects. The project Names, Managers of those projects, and estimated cost of those projects are given in columns B, C, and D respectively. We will count cells from our dataset by applying the COUNTIF function with multiple criteria in the same column. Here’s an overview of the dataset for today’s task.
1. Using the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Based on Text Value in Excel
The COUNTIF function is the first and foremost function to count cells with multiple criteria.
From our dataset, we will count cells based on text value that means according to the project name. Let’s follow the steps below.
Steps:
- First, select cell D16 to count cells that contain the name of the projects PMB, and PDB.
- Now, type the COUNTIF function in the Formula Bar. The COUNTIF function is,
=COUNTIF(B5:B14, "PMB") + COUNTIF(B5:B14, "PDB")- Where B5:B14 is the cell reference for both formulas. We have added two COUNTIF functions for two different columns based on their text value. The first COUNTIF we used to count the cost for PMB and the second one is to count the same for PDB.
- PMB and PDB is the name of the project.
- Further, press Enter on your keyboard and you will be able to get the return of the COUNTIF function based on the project name and the return is 5.
2. Applying the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Values in Excel
In this method, we will apply the COUNTIF function to count cells with multiple criteria in the same column between two values. From our dataset, we will count cells whose value is between $750000 and $900000. To do that, follow the instructions below.
Steps:
- First of all, select cell D16 to count cells that contain the cost of the projects between $750000 and $900000.
- After that, type the COUNTIF function in the Formula Bar. The COUNTIF function is,
=COUNTIF(D$5:D$14, ">750000")-COUNTIF(D$5:D$14,">900000")- Where D$5:D$14 is the cell reference, and the cell reference is absolute as we used the absolute cell reference($) sign.
- The first COUNTIF function counts cells whose values are greater than $750000 and the second COUNTIF function counts cells whose values are less than $900000.
- The minus(-) sign is used to subtract two functions’ output.
- Hence, press Enter on your keyboard and you will be able to get the return of the COUNTIF function based on the project name and the return is 5.
Similar Readings
- COUNTIF with Multiple Criteria in Different Columns in Excel
- COUNTIF Between Two Values with Multiple Criteria in Excel
3. Performing the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Dates in Excel
Let’s say, our dataset provides the deadline for several projects. We will apply the COUNTIFS function to count cells that value between 5/1/2020 and 8/5/2021. From our dataset, we will count cells whose value is between. To do that, follow the instructions below.
Steps:
- First of all, select cell D16 to count cells that contain between two dates of the projects.
- After that, type the COUNTIFS function in the Formula Bar. The COUNTIFS function is,
=COUNTIFS($C$5:$C$14, ">=5/1/2020", $C$5:$C$14, "<=8/5/2021")- Where $C$5:$C$14 is the cell reference, and the cell reference is absolute as we used the absolute cell reference($) sign.
- >=5/1/2020 is used for cells whose date is greater than 5 May 2020, and <=8/5/2021is used for cells whose date is greater than 8 May 2021.
- Hence, press Enter on your keyboard and you will be able to get the return of the COUNTIFS function based on the project name and the return is 6.
4. Combining the SUM and COUNTIF Functions with Multiple Criteria in the Same Column in Excel
In this method, we’ll count specified criteria by applying the SUM and COUNTIF functions. From our dataset, let’s say, we will count the name of the project manager named Vinchant and Anny. Counting the total names of Vinchant and Anny is an easy task. To do that, please follow the instructions below.
Steps:
- First, select cell D16 to count cells that contain the names of the project managers Vinchant and Anny.
- After that, type the SUM and COUNTIF function in the Formula Bar. The functions are,
=SUM(COUNTIF(C5:C14,{"Vinchant";"Anny"}))- Inside the COUNTIF function, C5:C14 is the cell range, and this function works with the AND Logic. Vinchant and Anny are the criteria1 and criteria2 of the COUNTIF function.
- The SUM function will sum up the total criteria that have been imputed inside the COUNTIF function.
- Further, press Enter on your keyboard and you will be able to get the return of the SUM and COUNTIF functions based on the project manager’s name and the return is 5.
Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel
5. Using the COUNTIF Function with OR Logic with Multiple Criteria in the Same Column
Last but not least, we’ll use the COUNTIFS function with OR logic with multiple criteria. This is the easiest and the most time-saving way. We’ll count the cells that contain DPD, PMB, and PDB project names and the corresponding project manager with OR Logic. Let’s follow the steps below to learn!
Steps:
- First, select cell D17.
- After that, type the COUNTIFS function in the Formula Bar. The COUNTIFS function is,
=COUNTIFS(B5:B14, {"DPD";"PMB";"PDB"},C5:C14,{"Vinchant";"Anny";"Catthy"})- Where B5:B14 is the cell reference we will find out the project name DPD, PMB, and PDB.
- C5:C14 is used for finding out the project manager’s name corresponding to those project that has been assigned in column B.
- Hence, press Enter on your keyboard and you will get your desired output by using the COUNTIFS function that has been given below screenshot.
Things to Remember
👉 #NAME error happens while typing incorrectly the range name.
👉 The #REF! error occurs when a cell reference is not valid.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to count cells with multiple criteria in the same column will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Article
- 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
- SUMPRODUCT and COUNTIF Functions with Multiple Criteria
<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
These examples are great but they dont cover my scenario exactly. In your example above, suppose a project can have 2 or more project managers, how can you know how many projects are shared among each pair of project managers. I guess the answer would be displayed in a matrix.
eg
Proj1 User1
Proj1 User2
Proj2 User3
Proj2 User1
Proj3 User2
Proj4 User3
Hello, ROB!
Thanks for sharing your problem with us!
Let’s say, we have a dataset that contains several projects and project managers. You can know how many projects are shared among each pair of project managers by applying the IF, COUNTIF, ROW, INDEX, and SMALL functions. Hence, you can concatenate the projects with project managers using the Ampersand symbol.
Let’s follow the instructions below to solve your problem!
→ First of all, Select cell F5 and write down the below screenshot’s functions in that cell. Hence, press Enter on your keyboard to get your desired output.
=IF(COUNTIF($B$4:$B$16,$E$5)>=ROWS($1:1),INDEX($C$4:$C$16,SMALL(IF($B$4:$B$16=$E$5,ROW($4:$16)),ROW(1:1))),"")→ Further, AutoFill the functions to the rest of the cells in column F.
→ Now, we will concatenate a project and the project managers under that project. Insert the below formula into the formula bar.
=$E$5&" "&F5→ Hence, AutoFill the formula to the rest of the cells in column G.
Please download the Excel file for your practice.
https://www.exceldemy.com/wp-content/uploads/2022/10/COUNTIF-with-Multiple-Criteria.xlsx
Again Thank you for your comment.
Regards
Md. Abdur Rahim Rasel
Exceldemy Team