In general life we usually need to count the number among a range of values based on the criteria ‘greater than a fixed value and less than another value’. We may also need to sum the total values for the matched samples. In this case. we will use COUNTIF and SUMIF function to do the calculation. And these functions are frequently used in statistic analysis.

First we want to know the basic syntax of the two functions.

=COUNTIF (range, criterion)
=SUMIF (range, criteria, sum range)

Assume we are in a school and statistic the scores in an exam. Here is the list for ID and Score. We use this sample to illustrate the COUNTIF function and SUMIF.

1. COUNT cells that greater than and less than a number using COUNTIF Function

#1 In above assumption, we need to count the number which score is greater than a fixed value for example 60.

In a blank cell, input the formula, in this case, the range is B2:B11, the criteria is greater than 60, so we enter =COUNTIF (B2:B11,">60"). See screenshot below.

After entering, the number is auto counted. And the number is 7. 60 is not included in this counting as it only equals to the criteria. If you want contains 60 in this case, just edit the criteria as “>=60”.

Test result ‘greater than 60’:

Test result ‘equals and greater than 60’:

#2 Count the number which score is less than a fixed value for example 80.

The same way like count ‘greater than a value’, just input =COUNTIF(B2:B11,"<80").

#3 Now we try to count the number for score between a range like greater than 60 but less than 70.

Input formula =COUNTIF(B2:B11,">60") - COUNTIF(B2:B11,">70"), the range is B2:B11, but the criteria is “>60” and “>70”, be aware that for the second COUNTIF, the criteria

“>70” means “<=70”, let’s see the result.

65 and 70 are matched the criteria.

If we change 70 to 71, then do the same counting, only 65 matched the criteria, so then counting number is 1.

2. COUNT cells that greater than and less than a number using VBA Code

Now, moving on to the second method, we’ll explore the use of VBA code to dynamically count cells based on specific criteria. With VBA, we can create custom functions and automate the counting process, providing a more versatile solution tailored to our needs.

Press ‘Alt + F11‘ to open the Visual Basic for Applications  editor.

In the VBA editor, right-click on any item in the project explorer, hover over “Insert,” and select “Module.” This adds a new module to your project.

Copy and paste the following VBA code into the newly created module:

Function CustomCount(rng As Range, criteria As String) As Long
    Dim cell As Range
    Dim count As Long
    For Each cell In rng
        If Evaluate(cell.Value & criteria) Then
            count = count + 1
        End If
    Next cell
    CustomCount = count
End Function

Close the VBA editor by clicking the “X” button.

In any cell, enter the formula:

=CustomCount(B2:B11, ">60") - CustomCount(B2:B11, ">70")

Replace “B2:B11” with your desired range and adjust the criteria as needed.

Press Enter to apply the custom function, and the selected cell will display the count based on the specified conditions.

3. SUMIF Function

#1  In general, we also need to statistic the total score for the matched samples. So we need to use SUMIF as well. For example, we statistic the total score for those matching the criteria ‘greater than 85’.

Use the formula =SUMIF(B2:B11,">85"), then the result is loaded.

#2 Sometimes we may meet cases more complex. See example below.

In this case, there are two range. The first one is A2:A11, we use them to do criteria (filter score), the second one is B2:B11, we use them to do sum, so they are the sum range. Input the formula =SUMIF(A2:A11,">85",B2:B11). See screenshot below:

Then we get the result.

4. Video: COUNT cells that greater than and less than a number

This Excel video tutorial where we’ll explore two methods to count cells based on specific criteria. Join us as we dive into the first method using a formula based on the COUNTIF function, followed by the second method utilizing VBA code.

5. 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 SUMIF Function
  The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…