# How to Use COUNTIF with Multiple Criteria in the Same Column in Excel (5 Methods)

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.

## 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.

## Related Article

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

1. 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

Md. Abdur Rahim Rasel Oct 27, 2022 at 12:37 PM

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.
→ 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.