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 the 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)
Example 1 – COUNTIF 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.
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.
Read More: Excel COUNTIF to Count Cells Greater Than 1
Example 2 – Counting Between Two Dates with the COUNTIF Function
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.
Steps:
- Select cell F10.
- Insert the formula:
=COUNTIF(C7:C17,">="&E7)-COUNTIF(C7:C17,">"&F7)- Press Enter.
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
Example 3 – Counting Times Between Two Times with the COUNTIF Function
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.
Steps:
- Select cell F10 and insert the following:
=COUNTIF(C7:C15,">="&E7)-COUNTIF(C7:C15,">"&F7)- Press Enter.
Read More: How to Use COUNTIF Function in Excel Greater Than Percentage
Example 4 – Using COUNTIF Between Two Cell Values with Multiple Criteria
The below dataset contains some products, their in-stock, and sold info. We’ll count the number of products that are in stock but haven’t been sold at all.
Steps:
- Select cell G10.
- Insert this formula:
=COUNTIF(C7:C15,">0")-COUNTIF(D7:D15,">0")- Press Enter.
Read More: How to Use COUNTIF for Non Contiguous Range in Excel
Example 5 – Using COUNTIFS Instead of COUNTIF to Count Between Two Cell Values
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 the Practice Workbook
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 Calculate Frequency Using COUNTIF Function in Excel
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
