How to Use COUNTIF with Multiple Criteria in the Same Column in Excel

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.

excel countif multiple criteria same column


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.

Use the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Based on Text Value in Excel

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

Use the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Based on Text Value in Excel

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.

Apply the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Values in Excel

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

Apply the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Values in Excel


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.

Perform the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Dates in Excel

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

Perform the COUNTIF Function to Count Cells with Multiple Criteria in the Same Column Between Two Dates in Excel


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.

Combine the SUM and COUNTIF Functions with Multiple Criteria in the Same Column in Excel

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

Combine the SUM and COUNTIF Functions with Multiple Criteria in the Same Column in Excel

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.

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

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

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


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.


Related Article

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo