Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • Managing Conditional Formatting Rules in Excel
  • How To Insert and Customize Sparklines in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to sum a total in multiple Excel tables

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel COLUMN Function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get last column number in range in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Validations

  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only
  • Data validation must not exist in list
  • Excel Data validation only dates between
  • Excel Data validation date in specific year

Excel Data validation don’t exceed total

by

Using the example below, this tutorial shows how to create Data validation don’t exceed total in Excel.

Formula

=SUM(range)<=1000

Explanation

To allow only values that don’t exceed a set sum, you can use data validation with a custom formula based on the SUM function. In the example shown, the data validation applied to B5:B9 is:

=SUM($C$6:$C$9)<=1000

How this formula works

Data validation rules are triggered when a user adds or changes a cell value.

In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to.

Each time a number is entered, the validation is triggered. As long as the sum remains less than 1000, validation succeeds. If any entry causes the sum C6:C9 to exceed 1000, validation fails.

Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • Extract multiple matches into separate rows in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel

Date Time

  • Convert date to text in Excel
  • Get first Monday before any date in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • Add workdays no weekends in Excel
  • Calculate years between dates in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Map text to numbers in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel

General

  • AutoRecover file that was never saved in Excel
  • Sum by group in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • How to generate random date between two dates in Excel
  • Count cells that do not contain many strings in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning