Sum If Less Than or Equal To – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the SUMIFS Function to sum rows with data less than (or equal to) a specific value in Excel and Google Sheets.

Sum If Less Than in Excel


Sum if Less Than 0

The SUMIFS Function sums data rows that meet certain criteria. Its syntax is:

SUMIFS syntax

This example will sum all Scores that are less than zero.


SUMIFS Less Than 0

We include the criteria “<0” to sum all negative Scores. In this example, the sum_range and criteria_range are the same.

Sum if Less Than

This example sums the Revenue for all Order Numbers less than 527.


SUMIFS Less Than HardCoded

Sum if Less Than or Equal to

Instead if you’d like to include all Order Numbers less than or equal to, modify the criteria to be “<=527”:


Sum if Less Than – Cell References

Usually, it is bad practice to hard-code values into formulas. Instead, it is more flexible to use a separate cell to define the criteria’s value.


SUMIFS Less Than Reference

Now we add the logical operator within double quotes (“”) and use & to join the operator and the criteria value:


Other logical operators can be used in this formula, such as:

  • Equal to (“=”&E3)
  • Less than or equal to (example: “<=”&E3)
  • Greater than (“>”&E3)
  • Greater than or equal to ( “>=”&E3)
  • Not equal to (“<>”&E3)

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:


But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:


Read our article on Locking Cell References to learn more.

Sum if Less Than in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

sum if less than Google Sheet

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List