If the values to test are in A2:A6 and the amounts to add are in B2:B6, use =SUMIF(A2:A6,"<100",B2:B6) to add the amounts for rows where the value in column A is less than 100. The key is to put the less-than operator and limit together in the criteria argument.
How SUMIF works with a less-than condition
SUMIF evaluates one range against a condition and, for matching rows, adds the corresponding cells in a sum range. Its arguments are the range to check, the criterion, and—when the values to add are elsewhere—the sum range. The criterion is written as text, which is why a comparison such as less than 100 needs quotation marks inside the formula.
For the opening example, A2:A6 contains 45, 80, 100, 125, and 60. B2:B6 contains 10, 20, 30, 40, and 15. The condition accepts 45, 80, and 60, but rejects 100 and 125 because “less than” does not include the limit itself. Excel therefore adds 10, 20, and 15, returning 45.
In general, SUMIF is useful when the test and sum are linked row by row: for example, add invoice amounts where days overdue are below a limit, or total units sold where a discount percentage is below a threshold. It does not change or filter the source cells; it calculates a result from them.
Enter the criterion correctly
For a fixed limit, put the comparison operator and number inside quotation marks in the criterion argument. With the same example layout—test values in A2:A6 and amounts in B2:B6—the formula below adds amounts for values below 100:
=SUMIF(A2:A6,"<100",B2:B6)
The operator is part of the criterion text. You can change it to a different comparison when the question changes: “less than or equal to” includes the boundary, while “greater than” selects values above it. Keep the operator inside the quotation marks with the number. Do not type a comparison such as A2<100 as the criterion; SUMIF expects a criterion, not a row-by-row formula expression.
The third argument is optional if the cells being checked are also the cells to add. With numbers in A2:A6 and no separate amount range, this formula adds the values below 100 directly from that range:
=SUMIF(A2:A6,"<100")
For the sample values, the result is 45 + 80 + 60, or 185. When the test range and the values to total differ, include the sum range explicitly. Using the wrong or mismatched ranges is one of the most common reasons a formula returns a plausible but incorrect total.
Use a cell as the threshold
A cell-based limit is easier to change than a number typed into every formula. Suppose A2:A6 contains the values to test, B2:B6 contains the amounts to add, and D1 contains the threshold 100. Join the less-than operator to the cell reference with an ampersand:
=SUMIF(A2:A6,"<"&D1,B2:B6)
With the sample data, this returns 45. If D1 changes to 90, the formula recalculates using 90 as the cutoff; only values below 90 qualify. The ampersand joins the text operator with the value in D1 to create a criterion Excel can evaluate. Without the ampersand, Excel would treat the cell reference as part of a literal criterion rather than use its current value.
This approach is useful for reports where a reader selects a limit, such as a maximum unit price or a minimum performance target. Label the threshold cell clearly, and consider applying a number format that makes its units obvious. A limit of 100 dollars and a limit of 100 units are numerically identical to Excel, even if they mean different things to a person reading the sheet.
Combine the limit with other conditions
SUMIF handles one criterion. If the total should also depend on a category, location, or other field, use SUMIFS, which supports multiple conditions. For example, assume A2:A6 contains the values to test, B2:B6 contains amounts, and C2:C6 contains regions. If the regions are East, West, East, East, and East in order, this formula totals amounts below the limit only for East:
=SUMIFS(B2:B6,A2:A6,"<100",C2:C6,"East")
The result is 25: the first and fifth rows qualify, contributing 10 and 15. The third row is East but has a test value of exactly 100, so it is excluded. SUMIFS places the sum range first, followed by each criteria range and its matching criterion. Make sure each criteria range covers the same rows as the sum range.
Use SUMIFS when every condition must be true for a row to count. If the requirement is instead “below 100 or in the East region,” that is an either/or condition, and simply adding both criteria to SUMIFS would not express it. You may need separate totals, a helper column, or a more tailored formula. Being precise about whether conditions are combined with “and” or “or” prevents a frequent logic error.
Compare values with dates or inclusive limits
Excel stores valid dates as serial numbers, so a less-than condition can compare dates just as it compares other numbers. Assume A2:A6 contains Excel dates and B2:B6 contains amounts. If D1 contains a valid cutoff date, this formula adds amounts for dates earlier than the date in D1:
=SUMIF(A2:A6,"<"&D1,B2:B6)
Enter the cutoff as a real Excel date, not text that only looks like a date. You can type a date into D1 and format the cell as a date; Excel will then compare the underlying date values. The formula uses “earlier than,” so a row dated on the cutoff is excluded. To include all dates through a particular day, use the following day as the exclusive cutoff. This is especially helpful if the source cells contain times as well as dates: a timestamp later on the selected day is still earlier than midnight at the start of the next day.
The same distinction applies to numeric boundaries. For test values in A2:A6 and amounts in B2:B6, this version includes values equal to 100 as well as those below it:
=SUMIF(A2:A6,"<=100",B2:B6)
In the sample, it includes the third row too, so the result is 75. That is 10 + 20 + 30 + 15; the row with 125 remains excluded. Choose between “less than” and “less than or equal to” based on whether the boundary itself should count, rather than changing the threshold to try to compensate.
Common problems and ways to check the result
If a formula returns zero or a total that seems too small, first check whether the test values are stored as numbers. Imported data can contain numbers stored as text, which may not compare as expected. A quick check is to inspect a few cells, try sorting the column, and look for inconsistent alignment or warning indicators. Converting the source data to numbers may be necessary before relying on the total.
Also verify that the ranges are aligned. If A2:A6 is the criteria range, the corresponding sum range should cover the same five rows, such as B2:B6—not B3:B7. SUMIF can return a result even when ranges are misaligned, making this mistake easy to miss. Confirm that the first data row and last data row are the same in both ranges.
- Check the operator: A fixed criterion should include the comparison symbol, such as less than, inside the quoted criterion.
- Check the boundary: A value equal to the cutoff is excluded by “less than” and included by “less than or equal to.”
- Check the source values: Look for text-formatted numbers, unexpected spaces, or values in a different unit.
- Check blanks: If blank test cells should never qualify, clean them or add an explicit nonblank condition with a multi-criteria approach.
- Check errors: Error values in the ranges can affect calculations; inspect the source rows rather than assuming the condition will hide every error.
For a small sheet, calculate the qualifying rows manually or filter the test column to values below the threshold and compare the visible amounts. Filtering is a useful audit, but it does not replace checking the formula: the filter may use a different cutoff or include a boundary that the formula excludes.
SUMIF compared with alternatives
SUMIF is usually the clearest choice for one less-than condition and one total. Use SUMIFS when multiple conditions must all be met, such as a value below a limit and a matching department. For a one-off total, a filtered selection and a manual sum may be quick, but it is less maintainable: changing the data or cutoff can require repeating the steps.
A helper column can make complex rules easier to inspect. For example, a helper field could mark each row as qualifying, after which a separate total uses that marker. This adds a column and upkeep, but can make business rules visible to other spreadsheet users. For a straightforward numeric cutoff, however, a helper column is usually unnecessary.
Excel tables can also make a workbook easier to maintain as rows are added, because table references can expand with the data. The underlying SUMIF logic remains the same. In a table-based workbook, use references that clearly identify the test and amount columns, and check that the table’s totals or calculated formulas include newly added records.
Frequently Asked Questions
How do I sum values less than a number in Excel?
Use SUMIF with the values to check as the first range, a quoted less-than criterion as the second argument, and the range to add as the third argument. If the values to check are also the values to add, the sum range can be omitted.
How do I use a cell reference for the less-than limit?
Join the less-than operator to the threshold cell with an ampersand in the criterion argument. This lets you change the cutoff in the cell without editing the formula.
Does SUMIF less than include the cutoff value?
No. A strict less-than comparison excludes a value equal to the limit. Use a less-than-or-equal comparison if the boundary should be included.
Can SUMIF add values less than a date?
Yes, provided the cells contain valid Excel dates and the cutoff is also a valid date. A date earlier than the cutoff qualifies; a date on the cutoff does not qualify for a strict less-than condition.
What should I use for less than plus another condition?
Use SUMIFS when a row must meet the less-than condition and one or more additional conditions, such as a department or region. Check that all criteria ranges and the sum range cover corresponding rows.
Related
- Excel Row Limit: How Many Rows Can a Sheet Hold?
- Excel Date Shortcut: Insert Today’s Date with Ctrl+;
- Data Excel Skills: What They Are and How to Build Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Goal Seek Formula Alternatives That WorkExcel does not have a worksheet function called GOALSEEK; Goal Seek is a What-If Analysis command. The formula…
- Excel ABS Function: How to Return a Positive ValueTo turn a negative number into a positive one in Excel, use the ABS function: it returns the…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Lock a Number in Excel: Keep It Fixed in FormulasTo keep a number fixed when you copy a formula, make its cell reference absolute by adding dollar…
- Power Query Filter: How to Filter Rows in ExcelTo filter in Power Query, open the query in Power Query Editor, select the filter arrow in a…
- SUMIFS Excel Column Criteria: Examples and Rules ExplainedTo sum values in an Excel column only when another column meets a condition, use SUMIFS: =SUMIFS(D2:D8,A2:A8,"North"). Here,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.