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.

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

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

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

 

 

You might also like:

Sidebar