To sum if less than, we need to use the SUMIF Function and define our criteria. In this example, we will use the SUMIF Function to sum all of the values less than a specified value. We will also use a named range to demonstrate how this can used in place of a standard range of cells
=SUMIF(range,criteria)
Syntax Explanation:
- range – Range of cells that you want to apply the criteria against.
- criteria – Criteria used to determine which cells to add. This can be in the form a number, expression, text, or function.
Usage Demonstration
Let’s look at the following data set. We want to sum all of the value less than 0. This is achieved by using the following formula.
=SUMIF(A4:A14,"<0")
Sum if less than with Named Range
In this example, we will use a named range to instead of defining the cell range. This is done by creating a Named Range. The easiest way this is achieved is to highlight your range of cells, or cell, then enter a name in the box next to the formula bar. Please note that this name must not contain any spaces. For a complete understanding, refer to the named range tutorial.
Once named, we can use “Sales_dollars” in our formula as shown.
=SUMIF(Sales_dollars,"<0")
