How to COUNT and SUM greater than and less than a number in Excel?

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.

How to COUNT and SUM greater than and less than a number in Excel 1

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’:

How to COUNT and SUM greater than and less than a number in Excel 2

Test result ‘equals and greater than 60’:

How to COUNT and SUM greater than and less than a number in Excel 3

#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").

How to COUNT and SUM greater than and less than a number in Excel 4

#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.

How to COUNT and SUM greater than and less than a number in Excel 5

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.

How to COUNT and SUM greater than and less than a number in Excel 6

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’.

How to COUNT and SUM greater than and less than a number in Excel 7

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

How to COUNT and SUM greater than and less than a number in Excel 8

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

How to COUNT and SUM greater than and less than a number in Excel 9

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:

How to COUNT and SUM greater than and less than a number in Excel 10

Then we get the result.

How to COUNT and SUM greater than and less than a number in Excel 11

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])…

Leave a Reply