Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Apply COUNTIF Between Two Cell Values in Excel

In MS Excel there are several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with a number, date, or text values, containing specific words or characters, and specific time. In this article, we will discuss the various uses of COUNTIF between two cell values in Excel. If you are also curious about it download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


5 Suitable Examples to Use COUNTIF Function Between Two Cell Values in Excel

To demonstrate the examples, we consider a dataset of 10 people. We will set different types of criteria to use the COUNTIF function between two cell values. Our dataset is in the range of cells B5:C14.

πŸ“š Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


1. Apply COUNTIF Function to Count Cells by Comparing Numbers

In the first example, we will use the COUNTIF function to count numbers that are greater than our target value. Our desired value is in cell D5.

The steps to complete the example are given below:

πŸ“Œ Steps:

  • First of all, select cell E5.
  • Now, write down the following formula in the cell.

=COUNTIF(C5:C13,">="&D5)

  • Press Enter.

Apply COUNTIF Function Between Two Values to Count Cells by Comparing Numbers

  • You will see the number of values that are greater than $150.

Thus, we can say that our formula works perfectly, and we are able to use the COUNTIF function between two cell values in Excel.

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)


2. Counting Particular Time by Using COUNTIF Function

In this example, we are going to use the COUNTIF function to count the particular time that is greater than our desired time value. Our desired value is in cell D5.

Counting Particular Time by COUNTIF Function

The steps to do the operation are given as follows:

πŸ“Œ Steps:

  • First, select cell E5.
  • After that, write down the following formula in the cell.

=COUNTIF(C5:C13,">="&D5)

  • Then, press Enter.

Counting Particular Time by COUNTIF Function Between Two Cell Values

  • You will get the time values that are greater than 1 hour.

Hence, we can say that our formula works effectively, and we are able to use the COUNTIF function between two cell values in Excel.

Read More: COUNTIF Greater Than and Less Than [with Free Template]


3. Using COUNTIF Function to Count Cells Within a Range

In the following example, the COUNTIF function will help us to count the number of particular cells within a data range. Our desired data range is in the range of cells D5:D6.

The steps to do the operation are shown below:

πŸ“Œ Steps:

  • At first, select cell E5.
  • Afterward, write down the following formula in the cell.

=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)

  • Next, press Enter.

Using COUNTIF Function Between Two Cell Values to Count Cells within a Range

  • You will notice the function will count the number of values within our data range.

Therefore, we can say that our formula works precisely, and we are able to use the COUNTIF function between two cell values in Excel.

Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)


Similar Readings


4. Apply OR Operation Through COUNTIF Function

Here, we will do the OR operation by the COUNTIF function. To accomplish the operation, we have to take a different dataset of food items and their cost.

OR Operation Dataset

The steps to finish the OR operation are described below:

πŸ“Œ Steps:

  • Firstly, select cell D5.
  • Next, write down the following formula in the cell.

=COUNTIF(B5:B10,"Pasta")+COUNTIF(B5:B10,"*salad")

  • After that, press the Enter key.

OR Operation Through COUNTIF Function Between Two Cell Values

  • You will get the desired result.

So, we can say that our formula works fruitfully, and we are able to use the COUNTIF function between two cell values through the OR operation in Excel.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


5. Combination of SUMPRODUCT and COUNTIF Functions with OR Logic

In the last example, we will the SUMPPRODUCT and COUNTIF functions to count numbers and apply the OR logic. To do this, we will use another different dataset of 10 employees and their IDs. We will calculate the number of duplicate IDs and unique IDs by the functions.

The steps to do the OR logic are shown as follows:

πŸ“Œ Steps:

  • First of all, select cell D5 to calculate the duplicate ID numbers.
  • For that, write down the following formula in the cell.

=SUMPRODUCT((COUNTIF(B5:B14,B5:B14)>1)*(B5:B14<>""))

  • Then, press Enter.

Combination of SUMPRODUCT and COUNTIF Functions with OR Logic to Find Duplicate IDs

  • After that, we have to calculate the unique ID.
  • Now, select cell E5 and write down the following formula in the cell.

=SUMPRODUCT((COUNTIF(B5:B14,B5:B14)=1)*(B5:B14<>""))

  • Again, press Enter.

Combination of SUMPRODUCT and COUNTIF Functions with OR Logic to Find Unique IDs

  • You will get both values at our desired cells.

Finally, we can say that our formula works successfully, and we are able to use the COUNTIF function between two cell values by the OR logic in Excel.

Read More: How to Use COUNTIF to Count Cells Greater Than 0 in Excel


Using COUNTIF Function to Count Value for Multiple Criteria

In addition, to the earlier examples, we are going to demonstrate three more closely related examples here. Among them, in the first example, we are going to use the COUNTIF function to count values for multiple criteria. Our criteria are in the range of cells D5:D6.

The procedure is explained below step-by-step:

πŸ“Œ Steps:

  • At the start, select cell E5.
  • After that, write down the following formula in the cell.

=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)

  • Press Enter.

Using COUNTIF Function to Count Value for Multiple Criteria

  • You will get the number of entities lies between $150 and $600.

At last, we can say that our formula works perfectly, and we are able to use the COUNTIF function between two cell values for multiple criteria in Excel.


Finding Values Between Two Dates by COUNTIF Function

In this example, we will find values between two dates by the COUNTIF function. Our desired dates are in the range of cells D5:D6.

The process is described below:

πŸ“Œ Steps:

  • First, select cell E5.
  • Now, write down the following formula in the cell.

=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)

  • Press Enter.

Finding Values Between Two Dates by COUNTIF Function

  • Finally, you will get the number of entities lying between 1 hour and 2 hours.

In the end, we can say that our formula works effectively, and we are able to use the COUNTIF function between two date cell values in Excel.


Use of COUNTIF Function for Two Numerical Numbers

In the last example, we are going to find the number of numerical values between two numbers through the COUNTIF function. Our desired numerical numbers are in the range of cells D5:D6.

The method is explained below step-by-step:

πŸ“Œ Steps:

  • At first, select cell E5.
  • Afterward, write down the following formula in the cell.

=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)

  • Next, press Enter.

Use of COUNTIF Function for Two Numerical Numbers

  • You will find the number of entities lying between numbers 100 and 500.

Finally, we can say that our formula works successfully, and we are able to use the COUNTIF function between two numerical cell values in Excel.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to the COUNTLIF function between two cell values in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo