If total is less than or equal to

This tutorial shows how to test if a sum of numbers from a range of cells is less than or equal to a specific number and return a value if the test is True or False through the use of an Excel formula, with the IF and SUM functions

EXCEL FORMULA 1. If total is less than or equal to

EXCEL

Hard coded formula
If total is less than or equal to
Cell reference formula
If total is less than or equal to
=IF(SUM(B5:B9)<=500,"Include","Exclude")
=IF(SUM(B10:B14)<=C5,C6,C7)
GENERIC FORMULA

=IF(SUM(range)<=number,value_if_true, value_if_false)

ARGUMENTS
number: A number that you want to test a range against for being less than or equal to the total of the values in that range.
range: A range that contains the numbers to calculate the total and test.
value_if_true: Value to be returned if the sum of the range that is being tested is less than or equal to a specified number.
value_if_false: Value to be returned if the sum of the range that is being tested is greater than a specified number.

GENERIC FORMULA

=IF(SUM(range)<=number,value_if_true, value_if_false)

ARGUMENTS
number: A number that you want to test a range against for being less than or equal to the total of the values in that range.
range: A range that contains the numbers to calculate the total and test.
value_if_true: Value to be returned if the sum of the range that is being tested is less than or equal to a specified number.
value_if_false: Value to be returned if the sum of the range that is being tested is greater than a specified number.

EXPLANATION

This formula uses the IF and SUM functions test if a total of the numbers in a range is less than or equal to a specific number and return a value if the test is True or False.
Click on either the Hard Coded or Cell Reference button to view the formula that has the number that you want to test the range against for being less than or equal to the sum of the values in the range and the return values directly entered into the formula or referenced to a specific cell.

In this example the formula initially calculates the total of the numbers in the range B5 to B9 (hard coded example) or B10 to B14 (cell reference example), which add up to 760. Then the formula performs a test to identify if this total is less than or equal to 500 and return a text value of "Include" otherwise it will return a text value of "Exclude". Given this total of these numbers is greater than 500 the formula will return a text value of "Exclude".

RELATED TOPICS

Related Topic Description Related Topic and Description
If total is greater than How to test if a sum of numbers from a range of cells is greater than a specific number and return a value if the test is True or False
If total is less than How to test if a sum of numbers from a range of cells is less than a specific number and return a value if the test is True or False
If total is greater than or equal to How to test if a sum of numbers from a range of cells is greater than or equal to a specific number and return a value if the test is True or False

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
SUM Function The Excel SUM function returns the sum of all numbers in a specified range