COUNTIF Between Two Cell Values in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

COUNTIF between two cell values in Excel means counting the number of cells that fall within a specific range of two values using the Excel COUNTIF function.

In this Excel tutorial, we’ll learn how to use the COUNTIF function to count occurrences between two cell values in Excel.

In the following overview image, we have used the COUNTIF function to count the number of cells within the range C5:C13 only if they fall between 70 and 80.

COUNTIF between Two Cell Values in Excel

We can use COUNTIF to count between two numbers; two dates; two times; and two cell values with multiple criteria in Excel. Moreover, we’ll show an alternative to COUNTIF.


What Is COUNTIF Function?
COUNTIF Between Two Numbers
Counting If Between Two Dates
Counting If Time Is Between Two Times
COUNTIF Between Two Cell Values with Multiple Criteria
COUNTIFS Instead of COUNTIF


What Is COUNTIF Function?

The COUNTIF function counts the number of cells within a range that meets the given condition.

The syntax for the function is:

=COUNTIF(range, criteria)

In the following overview image, the COUNTIF function counts the number of cells in the range C8:C16 with scores greater than 80. The formula used is:

=COUNTIF(C8:C16, ">"&F7)

overview of countif function


1. COUNTIF Between Two Numbers in Excel

In this section, we’ll apply the COUNTIF function to count occurrences between two numbers in Excel.

The below dataset contains some names and their scores. We have specified two scores in cells E7 and F7. We’ll count the number of cells within the range C7:C15 that are between the mentioned scores.

dataset of names and scores

So follow the steps below.

Steps:

  • Select cell F10 => Insert the formula:
=COUNTIF(C7:C15,">="&E7)-COUNTIF(C7:C15,">="&F7)

Here, we’ll count the scores that are greater than or equal to 70 and less than 80.

  • Press Enter. It’ll return the accurate count.

countif between cell values of two numbers

Read More: Excel COUNTIF to Count Cells Greater Than 1


2. Counting If Between Two Dates with COUNTIF Function

In this section, we’ll use the Excel COUNTIF function to count cells containing dates if the date falls between two specified dates.

The below dataset contains some Holidays and corresponding dates. We have specified two dates in cells E7 and F7. We’ll count the number of holidays present between the mentioned dates.

dataset of holidays

Follow the steps below to accomplish the task.

Steps:

  • Select cell F10 => Type the formula:
=COUNTIF(C7:C17,">="&E7)-COUNTIF(C7:C17,">"&F7)
  • Press Enter. It’ll return the count.

countif between two cell values containing dates

If we want to use COUNTIF between today and another date, the formula to be used is:

=COUNTIF(C7:C17,">="&TODAY())-COUNTIF(C7:C17,">"""&F6)

countif between today and another date

Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero


3. Counting If Time Is Between Two Times with COUNTIF Function

In this section, we’ll use the COUNTIF function for counting cells containing time if the time falls between two specified times.

The below dataset contains some names and submission times of their assignments. We have specified two times in cells E7 and F7. We’ll count the number of submissions made between the mentioned times.

dataset containing time

Learn the steps below to complete the task.

Steps:

  • Select cell F10 => Write the formula:
=COUNTIF(C7:C15,">="&E7)-COUNTIF(C7:C15,">"&F7)
  • Press Enter. It’ll return the count.

countif between two time

Read More: How to Use COUNTIF Function in Excel Greater Than Percentage


4. Using COUNTIF Between Two Cell Values with Multiple Criteria

In this section, we’ll apply the COUNTIF function to count occurrences between two cell values with multiple criteria in Excel.

The below dataset contains some products, their in-stock, and sold info. Here we’ll count the number of products that are in stock but haven’t been sold a single item.

dataset containing product and their stock and sold info

So follow the steps below.

Steps:

  • Select cell G10 => Type the formula:
=COUNTIF(C7:C15,">0")-COUNTIF(D7:D15,">0")
  • Press Enter. It’ll return the count.

countif with multiple criteria

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


5. Using COUNTIFS Instead of COUNTIF to Count Between Two Cell Values

In this section, we’ll apply the COUNTIFS function instead of COUNTIF to count between two cell values in Excel.

The below dataset contains some names and their scores. We have specified two scores in cells E7 and F7. We’ll count the number of cells within the range C7:C15 that are between the mentioned scores.

The formula is:

=COUNTIFS(C7:C15, ">="&E7, C7:C15, "<"&F7)

countifs between two cell values

Another alternative is to use the SUM function:

=SUM(((C7:C15>=E7)-(C7:C15>=F7)))

Read More: How to Use COUNTIF Function to Calculate Percentage in Excel


Download Practice Workbook


This article has shown how to use COUNTIF between two cell values in Excel. The cases include: between two numbers, dates, and time. We can also use COUNTIF between two cell values with multiple criteria. Leave a comment for any further queries.


Related Articles


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo