This guide will discuss how to sum if less than in Excel using the
The rules for using the
SUMIF function in Excel are the following:
SUMIFfunction is used to return the sum of cells that will meet a single condition. Furthermore, the function only supports one condition.
- When the sum_range argument is left blank or omitted, the cells in range will be summed.
- The non-numeric criteria must always be enclosed in double quotes. However, cell references in the criteria are not enclosed in double-quotes.
- The function supports wildcard characters such as? and * in the criteria. If we want to match a literal question mark or asterisk, we need to use a tilde (~). For example, ~? or ~*.
SUMIFfunction requires a range. So we cannot substitute an array.
Excel is a popular tool to use for different situations and purposes. Since it has several built-in functions and tools, we can perform difficult tasks. For instance, we can easily manipulate and calculate certain data in Excel.
Specifically, we will focus on learning how to use the
SUMIF function to sum if less than a certain value in Excel. Moreover, the
SUMIF function is one of the most useful built-in functions in Excel. So it allows us to sum values given that it meets the specific condition or criteria we set.
And this is most useful when we want to sum only certain values in our data set. In this case, we want to only sum values that are less than a certain value we set.
Let’s take a sample scenario wherein we need to sum if less than a certain value in Excel.
Suppose you are recording the test scores of your students. And you want to create a summary of the scores table to quickly display the different categories of scores in the class.
For example, you want to get the total number of students with a score of less than 60. To do this easily, you used the
SUMIF function and set the criteria to less than 60.
Before we move on to a real example of how to sum if less than in Excel, let’s first learn how to write the
The Anatomy of the SUMIF Function
The syntax or the way we write the
SUMIF function is as follows:
=SUMIF(range, criteria, [sum_range])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we start any function in Excel.
- SUMIF() is our
SUMIFfunction. And this is used to add the cells specified by a given single criterion or condition.
- range is a required argument. So this refers to the range of cells we want to evaluate for the given condition or criteria.
- criteria is another required argument. And this refers to the condition or criteria we set which can be in the form of a number, expression, or text value that will define the cells to be added.
- sum_range is an optional argument. So this refers to the actual cells to be summed. When omitted, the cells in the selected range will be used.
Note: If we want to input more than one condition or criteria, we must use the
Great! Now we can dive into a real example of how to sum if less than in Excel.
A Real Example of How to Sum If Less Than in Excel
Let’s say we have a class record showing the different test scores of the students. So our data set would have two columns displaying the test score and the number of students with that score. And our initial data set would look like this:
So our goal is to create a table to show the total number of students who scored below the passing score. For instance, we want to get the sum of students that have scored less than the passing score of 50.
To do this, we need to use the
SUMIF function and input a logical operator in the criteria. So a logical operator is used to compare two values. In Excel, there are six logical operators. And these are more than, less than, equal to, not equal to, more than or equal to, and less than or equal to.
Additionally, here is a table to easily learn the appropriate logical operators to use.
|Logical Operator Symbol
|Not equal to
|More than or equal to
|Less than or equal to
Since we want to sum the number of students that scored less than a certain value, we will be utilizing less than. Furthermore, we can create our entire summary of scores using the different logical operators.
For example, we want to mainly show the total number of students who scored less than the passing score, which is 50. Then, we also want to display the number of students with test scores greater than the passing score.
To do this, we can simply combine the
SUMIF function with the less than and greater than operators. In this case, we will input the criteria <50 to sum the test scores that are less than 50. Then, we will also input another formula with the criteria of >50 to sum scores greater than 50.
So we have two columns in the data set. One is a column showing the test scores. And the other column has the number of students that has that score. In the formula, we will select the column containing the test scores as the range we want to evaluate.
Next, we will select the column containing the number of students, which is the range we want to add. So the formula would evaluate the first column to find cells that match our criteria of <50. Then, the formula would add the numbers found in the second column that matched our criteria.
Thus, it would return the total number of students that scored less than the passing score. So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can proceed and discuss the process of how to sum if less than in Excel.
How to Use Sum If Less Than in Excel
In this section, we will discuss the step-by-step process of how to sum if less than in Excel using the
1. Firstly, we will create a new table to display the results. In this case, we want to sum if less than the passing score. Additionally, we want to sum if greater than the passing score.
Then, we can use the formula to first get the less than value. To do this, we will type in the formula “=SUMIF(B2:B9,”<50″,C2:C9)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will also get the greater than value. To do this, we will input the formula “=SUMIF(B2:B9,”>50″,C2:C9)”. Next, we will press the Enter key to return the result.
3. And tada! We have successfully summed if less than in Excel.
And that’s pretty much it! We have successfully discussed how to sum if less than in Excel using the
SUMIF function. Now you can use this method and apply it to your work whenever necessary.
Are you interested in learning more about what Excel can do? You can now use the
SUMIF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.