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.
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)
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.
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.
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.
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.
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)
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.
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.
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.
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.
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)
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
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!