To count the number of cells that contain values between two numbers in a range of cells, you can use the COUNTIFS function. In the generic form of the formula (above) range represents a range of cells that contain numbers, X represents the lower boundary, and Y represents the upper boundary of the numbers you want to count.
In the example, the active cell contains this formula:
The COUNTIFS function is built to count cells that meet multiple criteria. In this case, because we supply the same range for two criteria, each cell in the range must meet both criteria in order to be counted.
Using COUNTIF instead
If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:
The first COUNTIF counts the number of cells in a range that are greater than or equal to X (which by definition also includes values greater than Y, since Y is greater than X). The second COUNTIF counts the number of cells with values greater than Y. This second number is then subtracted from the first number, which yields the final result - the number of cells that contain values between X and Y.
Making the criteria variable
If you want to use a value in another cell as part of the criteria, use the ampersand (&) character to concatenate like this:
To count the number of cells that contain dates between two dates, you can use the COUNTIFS function. In the example shown, F5 contains this formula: = COUNTIFS ( dates , ">=" & DATE ( E5 , 1 , 1 ), dates , "<=" &...
Note: Excel contains many built-in "presets" for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this article. If you...
The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,...
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...
Excel Formula Training
Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!