Excel Formulas - Count cells between numbers

Count cells between numbers

Syntax of used function(s)

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Explanation

To count the number of cells that contain grade point between two given number, COUNTIFS function can be used. In the following example, F8 contains this formula:

Formula

=COUNTIFS(C5:C10,">=60",C5:C10,"<=70")

The above fornula contains the grade point of some employees, shown in the range C5:C10.

How this formula works

The COUNTIFS function counts the number of cells that matches one or more criteria.
In this case, two numbers have been provided. The COUNTIFS function will check whether the grade point in the range C5:C10 are in the number range specified in the formula.

Count cells between two numbers using range name

Explanation

In the following example, F8 contains this formula. A range name "Grade_Point" have been used instead of range C5:C10.

Formula

=COUNTIFS(Grade_Point,">=60",Grade_Point,"<=70")

The above fornula contains the grade point of some employees, shown in the range name "Grade_Point".

How this formula works:

The COUNTIFS function counts cells that matches multiple criteria. Here we use the same range for two criteria. Each cell in the range must satisfy both criteria in order to be counted.

Grade_Point -> is the range name for the range C5:C10

Count cells between two numbers with criteria

Explanation

In the following example, E8 contains this formula. A range name "Grade_Point" have been used instead of range C5:C10. And two criteria variable G8 and H8 have been used. G8 contains the value >=60 and H8 contains the value <=70.

Formula

=COUNTIFS(Grade_Point,G8,Grade_Point,H8)

The above formula search each grade point in the range "Grade_Point" between two criteria variables and increase counting if matches both criteria and produced the result.

Count cells between two numbers using COUNTIF() function

Syntax of used function(s)

COUNTIF(criteria_range, criteria)

Explanation

In the following example, E8 contains this formula. Two COUNTIF() functions have used in this formula. A range name "Grade_Point" have used for criteria range and the criterias have used within double inverted quotes.

Formula

=COUNTIF(Grade_Point,">=60")-COUNTIF(Grade_Point,">70")

How this formula works

The first COUNTIF function counts cells that matches the grade point on or above 60 and the second COUNTIF function matches the grade point above 70. Therefore subtract the result of second criteria from the first criteria.

Count cells using COUNTIF() function with criteria variable

Explanation

In the following example, E8 contains this formula. The COUNTIF() functions have used in this formula. A range name "Grade_Point" have used for criteria range and a criteria variable have been added with criteria by concatination operator (&).

Formula

=COUNTIF(Grade_Point,">="&E8)

The above example counts number of grade point are on or above the grade point metioned in E8.

Previous: Excel Formulas - Count cells using not equal to operator
Next: Excel Formulas - Count cells using less than operator