SUMIFS Excel Less Than: Criteria and Examples

Formulas & functions · Updated November 18, 2026

To sum values in Excel only when a related value is less than a limit, use SUMIFS with a criterion such as "<100"; for a limit stored in a cell, join the less-than operator to that cell with "<"&E2. For example, =SUMIFS(C2:C6,B2:B6,"<100") adds values in C2:C6 only for rows where the corresponding value in B2:B6 is below 100.

How a less-than SUMIFS criterion works

SUMIFS checks one or more criteria ranges row by row and adds the matching entries from a sum range. Its syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first argument is the range to add; each following pair identifies a range to test and the condition it must meet.

For a less-than test, the criterion is text containing the comparison operator and the threshold. A criterion of "<100" means strictly less than 100. A value equal to 100 does not qualify. The comparison is numeric when the cells contain numbers, so 99.99 qualifies and 100 does not.

Suppose B2:B6 contains 45, 80, 100, 125, and 60, while C2:C6 contains 10, 20, 30, 40, and 50. With those values in place, this formula returns 80: it adds 10, 20, and 50, the amounts on rows where column B is below 100.

With the test values in B2:B6 and amounts to sum in C2:C6, use:

=SUMIFS(C2:C6,B2:B6,"<100")

Use a cell as the threshold

A threshold in a worksheet cell is easier to change than a number typed into the formula. Assume B2:B6 contains the test values, C2:C6 contains amounts, and E2 contains 100. The ampersand joins the operator text to the value in E2, creating the criterion Excel needs. If E2 changes to 90, the same formula applies the new limit without editing its text.

With test values in B2:B6, amounts in C2:C6, and the threshold in E2, use:

=SUMIFS(C2:C6,B2:B6,"<"&E2)

The result is 80 when E2 is 100 and the example data above is used. This pattern is useful for budgets, score cutoffs, inventory limits, and any report where a user may adjust the boundary. If you copy the formula elsewhere, consider whether E2 should stay fixed. For a fixed threshold cell while copying, use an absolute reference such as $E$2; relative references adjust as formulas are copied.

Less than versus less than or equal to

The operator determines whether the boundary itself is included. Use less than when values exactly equal to the limit should be excluded. Use less than or equal to when the limit should qualify as well. This distinction matters in grading bands, spending caps, and date cutoffs: a strict limit of 100 treats 100 differently from a maximum of 100.

With test values in B2:B6, amounts in C2:C6, and the limit of 100 in E2, the inclusive version is:

=SUMIFS(C2:C6,B2:B6,"<="&E2)

That formula returns 110 for the example data: it adds the amounts beside 45, 80, 100, and 60. The strict less-than version returns 80 because it excludes the amount beside 100. Excel criteria can also use greater-than operators, so a criterion such as ">"&E2 selects values above the threshold. Choose the operator that expresses the business rule rather than changing the data to force a result.

Compare dates with a less-than condition

Excel stores real dates as serial numbers, which means SUMIFS can compare them with the same less-than logic used for numbers. A reliable way to build a date criterion is to use DATE(year,month,day), rather than typing a date inside quoted text. This avoids confusion from regional date formats such as month/day/year versus day/month/year.

Assume A2:A5 contains the dates April 28, April 30, May 1, and May 3, 2025, and B2:B5 contains 120, 80, 200, and 60. To sum amounts dated before May 1, use:

=SUMIFS(B2:B5,A2:A5,"<"&DATE(2025,5,1))

The result is 200: 120 plus 80. The May 1 entry is not included because the condition is strictly earlier than May 1. If the date threshold is in E2 instead, join the less-than operator to E2, just as you would with a numeric threshold.

Date cells can include times as well as dates. A condition earlier than May 1 excludes every timestamp on May 1, which is often what a reporting cutoff requires. To include all times on a particular day, a common approach is to set an upper boundary at midnight on the following day and use a less-than test against that boundary. Check that the source cells contain actual Excel date-time values, not text that only looks like a date.

Combine less than with other conditions

SUMIFS treats separate criteria pairs as AND conditions: a row must satisfy every condition to be included. You can combine a numeric limit with a region, status, product, or date test. The range for the less-than test may be the same as the sum range; in that case Excel tests the amount and adds it only when it meets the cutoff.

Assume A2:A6 contains East, East, West, East, East; B2:B6 contains Paid, Pending, Paid, Paid, Paid; and C2:C6 contains 70, 120, 50, 90, and 100. To total paid sales in East that are below 100, use:

=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Paid",C2:C6,"<100")

The result is 160, from the qualifying sales of 70 and 90. The 120 is not below 100, the 50 is in the West, and the 100 is equal to rather than less than the limit. This example shows why each criteria range must line up with the sum range: the conditions are evaluated against the corresponding row.

Choose between SUMIF and SUMIFS

For one condition, either SUMIF or SUMIFS can do the job. SUMIF uses the order SUMIF(range, criteria, [sum_range]), while SUMIFS places the sum range first and supports multiple criteria pairs. If a formula may gain another condition later, SUMIFS is often easier to extend. Its syntax also makes the range being added explicit at the start.

With test values in B2:B6 and amounts in C2:C6, either of these returns 80 for values below 100:

=SUMIF(B2:B6,"<100",C2:C6)

=SUMIFS(C2:C6,B2:B6,"<100")

Do not interchange the argument order. A frequent mistake is to write the SUMIF argument order when using SUMIFS, which can make Excel interpret the wrong range as the sum range or return an error. When there are multiple conditions, SUMIFS is the more direct choice because each additional condition is entered as another range-and-criterion pair.

Common errors and how to fix them

If a formula returns zero or a total that seems too small, check the values and ranges before rewriting the comparison. Numbers imported from another system may be stored as text; text that looks like 45 is not always a numeric 45. Dates can have the same problem. Convert or clean the source data when necessary, and confirm a representative cell is a true number or date rather than a text string.

  • Check the operator and boundary. The less-than operator excludes values equal to the cutoff. Use less than or equal to only when equality should count.
  • Check the criterion construction. A fixed cutoff can be written as a quoted criterion such as "<100". A cell reference needs concatenation, as in "<"&E2.
  • Check range dimensions. The sum range and every criteria range should cover corresponding rows and have matching dimensions. A range shifted by one row can produce a plausible but incorrect total.
  • Check for spaces or inconsistent labels. A region or status condition will not match labels that contain extra spaces or different spelling. Clean source labels if needed.
  • Check the data type. Numeric text, text dates, and amounts stored as text can prevent a numeric comparison or addition from working as expected.
  • Check your formula separator. Examples here use commas. Some Excel regional settings use semicolons between arguments; if Excel rejects a correctly structured formula, your locale may require that separator.

For a quick diagnosis, test one row manually: inspect its criterion value, ask whether it is truly below the cutoff, and verify that the amount on that same row is in the sum range. Then compare the full criteria ranges in the formula. This row-by-row check often reveals an off-by-one range, a text value, or an equality case that explains the difference.

Handle OR conditions with care

Multiple criteria pairs inside one SUMIFS are joined as AND, not OR. For example, adding both a requirement for region East and a requirement for region West does not mean either region can match; one row cannot normally be both. To total separate groups that each meet their own less-than condition, calculate a SUMIFS result for each group and add the results.

Suppose A2:A6 contains regions, B2:B6 contains values to test, and C2:C6 contains amounts. To add amounts for either East or West where the test value is below 100, use:

=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"<100")+SUMIFS(C2:C6,A2:A6,"West",B2:B6,"<100")

This works when the groups do not overlap, as regions normally do. With categories that can overlap, adding separate totals might count a row twice. Define the groups and check for overlap before combining results. For a small, fixed set of alternatives, separate SUMIFS calculations are also straightforward to audit.

Frequently Asked Questions

How do I write less than in an Excel SUMIFS formula?
Put the less-than operator and cutoff together as a criterion, such as "<100". If the cutoff is in a worksheet cell, concatenate the operator with the cell reference, such as "<"&E2.

Does SUMIFS less than include the number itself?
No. A strict less-than condition excludes values equal to the cutoff. Use a less-than-or-equal-to criterion if values equal to the limit should also be counted.

Can SUMIFS use a date as the less-than limit?
Yes. Excel dates are stored as numbers, so a less-than date criterion works. Building the cutoff with DATE(year,month,day) is a dependable way to avoid ambiguity about how a typed date is interpreted.

Why does my SUMIFS less-than formula return zero?
Common causes include numbers or dates stored as text, a threshold cell containing text, mismatched range sizes, or a criterion that excludes values equal to the cutoff. Check the underlying cell types and test a qualifying row against the formula’s exact ranges.

Can SUMIFS test less than and another condition at the same time?
Yes. Add a criteria-range and criterion pair for each additional requirement. Separate pairs work as AND conditions, so a row is summed only when it passes every test.

Should I use SUMIF or SUMIFS for one less-than condition?
Both can handle one condition. SUMIF is concise for a single test; SUMIFS uses the sum range as its first argument and is convenient when you expect to add more conditions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.