How to Count Cells between Two Numbers in Excel

This post will guide you how to count cells between two numbers using a formula in Excel 2013/2016. How do I count times between a given range cells based on a condition in Excel. Is there a easy way to count cells between two given numbers in a selected range of cells in Excel.

Count Cells between Numbers using COUNTIFS Function


Assume that you have a list of data in range A1:B6, and you want to count cells between two numbers. In this case, and you can use the COUNTIFS function to achieve the result of counting times between two given numbers in Excel. You should know that the COUNTIFS function will count the number of cells that meet one or more criteria. And the below tutorial will help all levels of Excel users in counting cells between two given numbers in your worksheet.

You can use the following formula based on the COUNTIFS function to count cells between 40 and 90.

=COUNTIFS(B2:B6,”>=40″, B2:B6,”<=90″)

count cells between two numbers1

From the above screenshot, and you can see that this formula will count sales number in range B2:B6 that are greater than or equal to 40 and less than or equal to 90. To count cells between two numbers, and you need to provide two criteria in COUNTIFS formula. One is for starting number and one is for the ending number.

Let’s see how this formula works:

The COUNTIFS function can be used to count cells that meet one or more criteria in the given range cells.

This formula has two criteria. It counts the cells in range of cells B2:B6 with values between 40 and 90. The operator “>=” means “greater than or equal to”, and “<=” means “less than or equal to”.

The formula will return the value “3”, which indicates to that there are three cells with values between 40 and 90 in your range of cells.

Count Cells between Numbers using COUNTIF Function


You can also use another function called COUNTIF in an older version of Microsoft Excel to achieve the result of counting cells between two numbers. For the example above you can use the below formula based on the COUNTIF function:

=COUNTIF(B2:B6,”>=40″)-COUNTIF(B2:B6,”>90″)

count cells between two numbers2

Let’s see that how this formula works:

The first COUNTIF function will count the number of cells in a range that are greater than or equal to “40”. And the second COUNTIF function will count the number of cells with values that are greater than “90”. And the second number is subtracted from the first result that returned by the first COUNTIF function. Then you should get the number of cells that contain values between 40 and 90.

The First COUNTIF function:

=COUNTIF(B2:B6,”>=40″)

count cells between two numbers3

The Second COUNTIF function:

= COUNTIF(B2:B6,”>90″)

count cells between two numbers4

Related Functions


  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

 

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Sidebar