Software

Use a function to count cells between two values in Excel

Are you ready to teach Excel a new trick? Mary Ann Richardson explains how to count the number of cells whose values fall between two particular numbers in a certain range automatically.

Excel has no built-in function that can count the number of cells whose values fall between two numbers in a range. Rather than writing a new formula every time you want to make that calculation, you can build your own function that you can use whenever you wish.

Let's say you've entered a list with the number of years your employees have been at the company in the range H1:H400. You want to know how many of them have been at the company between 10 and 20 years. You can build a function that finds the answer by following these steps:

  1. Press [Alt]F11.

  2. Enter the following code at the prompt:
    Function COUNTBETWEEN(rng, num1, num2)
    COUNTBETWEEN = Application.CountIf(rng, "<=" &
    num2) - Application.CountIf(rng, "<" & num1)
    End Function
  3. Press [Alt]F11.

To use the function, enter the following formula in a blank cell:

   =COUNTBETWEEN(H1:H400,10,20)

The function will return the number of employees whose tenure is greater than or equal to 10 (num1) and less than or equal to 20 (num2). You can subsequently use this function on any other range of data.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

0 comments