We have a dataset that contains information about several projects. The project names, managers of those projects, and estimated costs of those projects are given in columns B, C, and D. We will count cells from our dataset by applying the COUNTIF function with multiple criteria in the same column.
Method 1 – Using the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Based on Text Value
Steps:
- Select cell D16 to count cells that contain the name of the projects PMB, and PDB.
- 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.
- Press Enter and you will be able to get the return of the COUNTIF function based on the project name and the return is 5.
Method 2 – Applying the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Values
Steps:
- Select cell D16 to count cells that contain the cost of the projects between $750000 and $900000.
- 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.
- Press Enter 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
Method 3 – Performing the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Dates
Steps:
- Select cell D16 to count cells that contain between two dates of the projects.
- 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.
- Press Enter and you will be able to get the return of the COUNTIFS function based on the project name and the return is 6.
Method 4 – Combining the SUM and COUNTIF Functions with Multiple Criteria in the Same Column
Steps:
- Select cell D16 to count cells that contain the names of the project managers Vinchant and Anny.
- Type the SUM and COUNTIF function in the Formula Bar. The functions are:
=SUM(COUNTIF(C5:C14,{"Vinchant";"Anny"}))
Formula Breakdown
- 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.
- Press Enter 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
Method 5 – Using the COUNTIF Function with OR Logic with Multiple Criteria in the Same Column
Steps:
- Select cell D17.
- 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.
- Press Enter 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 the Practice Workbook
Download this workbook to practice.
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
- 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