Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to count number of cells between two values or dates in Excel?

Author Siluvia Last modified

When working with data in Excel, it is often necessary to determine how many cells fall within a specified range of values or dates. For example, you might need to find out how many test scores lie between two set thresholds, or count records within a particular timeframe. Accurately retrieving these counts can support data analysis, reporting, and decision-making. This tutorial provides step-by-step guidance on how to count the number of cells between two values or between two dates in Excel, using built-in formulas, user-friendly add-ins, and advanced automation with VBA code.

Count cell numbers between two numbers with CountIf function
Count cell numbers between two dates with CountIf function
Count cell numbers between two values or dates with Kutools for Excel (without formula)
VBA Code - Automate counting of cells between two values or dates using a macro

Count cell numbers between two numbers with CountIf function

Suppose you have a worksheet with numbers listed in the range B2:B8, and you wish to calculate how many of these numbers fall between 75 and 90. This is a common requirement in scenarios such as analyzing exam scores, evaluating sales performance within targets, or identifying relevant data ranges for further calculation.

a screenshot of the original data

Counting cell numbers > X and < Y

If your goal is to count cells in B2:B8 that are greater than 75 and less than 90 (excluding 75 and 90 themselves), follow these steps:

1. Select a blank cell where you want the result to appear. Then, copy and paste the following formula into the Formula Bar and press Enter:

=COUNTIFS(B2:B8,">75", B2:B8,"<90")

screenshot of using formula to count the number cells > x and < y

The selected cell will immediately display the count of cells that satisfy your criteria.

Practical notes: If you need to apply this to other ranges, adjust the cell addresses in the formula accordingly. Also, double-check the comparison signs to ensure you are either including or excluding the threshold values as needed for your scenario.

Counting cell numbers >= X and <= Y

To include the boundary values (75 and 90 in this example), use the following formula, which counts all cells in the range B2:B8 whose values are greater than or equal to 75 and less than or equal to 90:

=COUNTIFS(B2:B8,">=75", B2:B8,"<=90")

As before, select a blank cell, enter the above formula in the Formula Bar, and press Enter. The result shown will include cells with values exactly equal to75 or90, making this suitable for cases where inclusive boundaries are important, such as grading systems or eligibility assessments.

screenshot of using formula to count the number cells >= x and <= y

Tip: These formulas can be used for any continuous numeric range. Remember to verify the correct logic if you are adapting to other number intervals or non-contiguous ranges.

Easily count number of cells between two values or dates in Excel:

Click Kutools > Select > Select Specific Cells. The Kutools for Excel Select Specific Cells utility helps you easily count and select cells between two values or dates, streamlining your workflow especially when formulas become complex.
Download the full feature 30-day free trail of Kutools for Excel now!

a screenshot showing how to use Kutools for Excel to easily count the number of cells between two values or dates

Count cell numbers between two dates with CountIf function

Counting cell numbers between two dates is a frequent task for those tracking attendance, project schedules, or any period-based data. This method is especially helpful where date ranges change or reference other cells for flexibility.

Suppose you have a date range in cells A14:A20, and you would like to count the number of dates that fall between the dates specified in cells B21 (start date) and B22 (end date). Here’s how you can accomplish this:

1. Select a blank cell for the output. Then copy and paste the formula below into the Formula Bar and press Enter:

=COUNTIFS(A14:A20,">="&B21,A14:A20,"<="&B22)

a screenshot of using COUNTIF to count number of cells between two dates

Notes:

  • Be sure to replace the ranges (A14:A20) and the cell references for the start and end dates (B21, B22) to match your actual data.
  • This formula counts all dates from the start date through the end date (inclusive).
  • Ensure all cells in your date range are formatted as dates for accurate results.

Error reminder: If your result is zero but you expect matches, check that the date formats in your range and condition cells are consistent, as mismatched formats can lead to unexpected outcomes.

Count cell numbers between two values or dates with Kutools for Excel (without formula)

If you find formulas to be difficult or prone to errors, you may prefer a direct approach that doesn't require entering or adjusting formulas. Kutools for Excel offers a visual method that can count (and optionally select) cells between two values or dates in just a few clicks, which is especially convenient in routine operations or when sharing steps with non-technical colleagues.

Before applying Kutools for Excel, please download and install it firstly.

Step1. Click Kutools > Select > Select Specific Cells.

Step2. In the Select Specific Cells dialog box, choose the range you wish to evaluate in the Select cells in this range section. Then, under Selection type, choose Cell. Specify your criteria, for example, "Greater than" 75 and "Less than" 90, under Specific type. When you have entered your conditions, click Ok.

a screenshot of setting conditions in the Select Specific Cells dialog box

Step3. A Select Specific Cells dialog will display the total number of qualifying cells. Confirm by clicking OK. The matched cells will be highlighted and selected immediately, making follow-up actions such as formatting or inspection simple.

a screenshot showing how many cells are eligible and selected

Note: This feature supports both number and date ranges. To count and select cells within a date interval, simply enter the two dates in the dialog as start and end criteria, as shown:

a screenshot of configuring date conditions in the dialog box

This visual and flexible approach is particularly effective for those who prefer not to work with formulas, or for validating formula-based results by visual inspection.

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

VBA Code - Automate counting of cells between two values or dates using a macro

For advanced users or situations where repeated or dynamic counting is required, VBA (Visual Basic for Applications) provides a programmable solution for counting cells between two values or dates. With VBA, you can quickly automate this counting task, making it suitable for larger datasets, custom logic, or batch operations where manual methods would be inefficient.

Some applications include data auditing, bulk report generation, or creating user-defined “count between” actions triggered by a button. The approach below allows you to specify your target range and the two threshold values or dates dynamically at runtime.

Follow these instructions:

1. Click Developer Tools > Visual Basic to open the VBA editor window. In the editor, click Insert > Module, then copy and paste the code below into the module window:

Sub CountCellsBetweenTwoValues()
    Dim WorkRng As Range
    Dim MinValue As Variant
    Dim MaxValue As Variant
    Dim CountResult As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to count:", xTitleId, WorkRng.Address, Type:=8)
    
    MinValue = Application.InputBox("Enter the lower boundary (number or date):", xTitleId, "", Type:=2)
    MaxValue = Application.InputBox("Enter the upper boundary (number or date):", xTitleId, "", Type:=2)
    
    If WorkRng Is Nothing Or MinValue = "" Or MaxValue = "" Then
        MsgBox "Operation cancelled or invalid input.", vbExclamation
        Exit Sub
    End If
    
    CountResult = 0
    Dim Cell As Range
    
    For Each Cell In WorkRng
        If IsDate(MinValue) And IsDate(MaxValue) And IsDate(Cell.Value) Then
            If Cell.Value >= CDate(MinValue) And Cell.Value <= CDate(MaxValue) Then
                CountResult = CountResult + 1
            End If
        ElseIf IsNumeric(MinValue) And IsNumeric(MaxValue) And IsNumeric(Cell.Value) Then
            If Cell.Value >= Val(MinValue) And Cell.Value <= Val(MaxValue) Then
                CountResult = CountResult + 1
            End If
        End If
    Next Cell
    
    MsgBox "The number of cells between " & MinValue & " and " & MaxValue & " is: " & CountResult, vbInformation, xTitleId
End Sub

2. To execute the macro, press F5 while the code is selected, or close the editor and run it from the Macros dialog in Excel. The macro will prompt you to select your counting range, then enter the two boundary values or dates. It will display a message box with the count of cells between your specified limits.

Tips and troubleshooting:

  • Input boundaries as numbers (e.g., 75 and 90) or dates in a recognizable format (e.g., 2024-01-01) for the most reliable results.
  • Both the start and end number/date will be included in the result.
  • If your range contains both numbers and dates, or contains text data, only the relevant typed entries will be counted by the logic in the macro.
  • If the macro reports zero but you expect matches, check your range and input types for consistency (all numbers, or all dates).
  • Macros can be reused or assigned to a button for quick repeated use in dashboards or recurring reports.

Advantages of the VBA approach: Highly customizable, saves time in repetitive tasks, and allows support for dynamic input. The main limitation is that VBA requires macro-enabled files and possibly user permissions to run.

You can further tailor the VBA code for different types of intervals (exclusive, inclusive, etc.) or integrate it into larger automated workflows depending on your practical needs.

Demo: Count cell numbers between two values or dates with Kutools for Excel

 

 

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in