How to Sum if Greater Than A Number in Excel

Sometimes we may meet the case that to sum numbers with the pre-condition “greater than X” exists. We only want to sum numbers which are greater than a supplied number. In this article, we will show you the method to resolve this problem by formulas with the help of Excel SUMIF and SUMIFS functions.

Through a simple instance below, we will introduce you the syntax, argument and the usage of SUMIF and SUMIFS functions. We will let you know how the formula works step by step to reach your goal clearly. After reading the article, you may have a simple understanding of SUMIF/SUMIFS functions.

EXAMPLE

How to Sum if Greater Than A Number in Excel 1

Refer to the left-hand table, we can see that there are two columns “Score” and “Count”. For each score, its adjacent cell records the number of “how many students get this score in an exam”.  In the right-hand table, it is a simple summary, there is one criterion that “score is greater than 70”, and the expectation is counting the total number of students whose score is greater than 70. Actually 70 is what we just entered into E2, we can also change it to another value.

In this instance, we want to sum numbers from “Count” column based on the criteria “Score > 70 (value in E2)”. We need to find out all scores that meet our requirement, and then sum up values in “Count” column accordingly. To resolve this issue by formula, we can apply Excel SUMIF or SUMIFS functions.

FORMULA 1 – APPLY SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a name for this range, just use the column header ‘Score’.

How to Sum if Greater Than A Number in Excel 2

Step 2: Select B2:B9, in Name Box define a name for this range, for example ‘Count’.

How to Sum if Greater Than A Number in Excel 3

Note: Above two steps are not required, they are optional, you can directly use cell or range reference like A2:A9, B2:B9 in your formula, but for understanding well, we name ranges in advance.

Step 3: In E3, enter the formula =SUMIF(Score,”>”&E2,Count).

How to Sum if Greater Than A Number in Excel 4

Note: In step#1 and step#2 we already name ranges “Score” and “Count”, so when entering the range information into formula, just after typing “Sc…”, our named range “Score” is loaded, just select it from dropdown list. As we explained in step#2, you can also hold and drag A2:A9 to fill the argument as well.

How to Sum if Greater Than A Number in Excel 5

Step 4: Press Enter after typing the formula.

How to Sum if Greater Than A Number in Excel 6

In column A, cell A6 (75), A8 (80) and A9 (85) are greater than 70, the corresponding counts are 2 (in cell B6), 4 (in cell B8), and 2 (in cell B9), so the total count is 2+4+2=8. The formula works correctly.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIF ARGUMENTS EXPLAINATION

SUMIF – RANGE

In this instance, we need to count numbers of student whose score is greater than 70. We need to compare each score in “Score” column with 70, so “A2:A9” is the ‘range’.

In the formula bar, select “Score”, press F9, all values in “Score” are listed in an array.

How to Sum if Greater Than A Number in Excel 7

SUMIF – CRITERIA

We enter “>”&E2 into criteria part. As we mention above, if logical operator is used, it should be quote with double quotes “”; In E2 we supply a certain value “70” to filter scores; to concentrate logical operator “>” and number “70”, we must to use a special character “&” to connect them, if “&” is missing, formula quits directly.

In the formula bar, select the complete criteria, press F9.

How to Sum if Greater Than A Number in Excel 8

SUMIF – SUM RANGE

In this instance, we need to count numbers of student whose score is greater than 70. So “Count” column range “B2:B9” is the ‘sum range’.

In the formula bar, select “Count”, press F9, all values in “Count” are listed in an array.

How to Sum if Greater Than A Number in Excel 9

SUMIF WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with the help of these arguments.

=SUMIF({60;65;70;50;75;55;80;85},”>70″,{2;5;6;5;2;3;4;2})

We have a pair of range and criteria:

RANGE: {60;65;70;50;75;55;80;85}

CRITERIA: “>70”

Compare each number in array with 70; if number can satisfy the condition “>70”, mark it bold in array:

{60;65;70;50;75;55;80;85} – Bold if it is >70

Now, for bold numbers, as they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others.

{Flase;False;False;False;True;False;True;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to make this array can be taken into calculation in next step.

{0;0;0;0;1;0;1;1}

Now, we have below two arrays:

{2;5;6;5;2;3;4;2} – Sum Range

{0;0;0;0;1;0;1;1} – from above steps we know that if number is greater than 70, 1 is displayed in this number’s position

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

How to Sum if Greater Than A Number in Excel 10

Sum up all numbers in the third row. You can use SUM function here and select all values in the third row as reference.

2+4+2=8

FORMULA 2 – APPLY SUMIFS FUNCTION

Now, let’s try to apply SUMIFS function to resolve this issue. The first two steps are the same.

Step 3: In E3, enter the formula =SUMIFS(Count,Score,”>”&E2).

How to Sum if Greater Than A Number in Excel 11

Note: We can see in SUMIFS formula, we use the same parameters compare with SUMIF, the difference is “Count” is moved to the first position.

Step 4: Press Enter after typing the formula.

How to Sum if Greater Than A Number in Excel 12

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it allows user provides multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

SUMIFS function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIFS ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIFS – SUM RANGE

How to Sum if Greater Than A Number in Excel 13

SUMIFS – CRITERIA RANGE

How to Sum if Greater Than A Number in Excel 14

SUMIFS – CRITERIA

How to Sum if Greater Than A Number in Excel 15

SUMIFS WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps exactly.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

Related Functions


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

 

Related Posts

How to Sum if Equal to Many Items or A Range in Excel
How to Sum if Equal to Many Items 12

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel
How to Sum if Equal to X or Y 19

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum if Contains an Asterisk
How to Sum in Last N Days 14

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk
How to Sum if Contains an Asterisk 7

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range
How to Sum in Vertical Range 8

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range
How to Sum in Horizontal Range9

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel
How to Sum with Criteria and Or Logic in Excel 8

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Sum by Formula If Cells Are Not Blank in Criteria Range
How to Sum by Formula If Cells Are Not Blank in Criteria13

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how ...

How to Sum by Formula if Cell Ends with in Excel
How to Sum by Formula if Cell Ends with in Excel14

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the ...

Sidebar