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.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 5 Suitable Ways to Use COUNTIF with Multiple Criteria in the Same Column in Excel

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. Use 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**.

**Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel**

### 2. Apply 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**.

### 3. Perform 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 of 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/2021**is 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. Combine 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**. To count the total name 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 name of the projects manager named**Vinchant**and**Anny.**

- After that, 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.

- 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: COUNTIF with Multiple Criteria in Different Columns in Excel (Both Single and Multiple Criteria)**

### 5. Use the COUNTIF Function with OR Logic with Multiple Criteria in the Same Column

Last but not the 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 where 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.

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

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,andSMALLfunctions. Hence, you can concatenate the projects with project managers using theAmpersandsymbol.Let’s follow the instructions below to solve your problem!

→ First of all, Select cell

F5and write down the below screenshot’s functions in that cell. Hence, pressEnteron 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,

AutoFillthe functions to the rest of the cells in columnF.→ 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,

AutoFillthe formula to the rest of the cells in columnG.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