Excel DATA VALIDATION using SUM Function

Figure 1. Excel DATA VALIDATION using SUM Function.

If we decide to restrict users to only input numeric values whose totally or sum does not exceed a specific amount within a selected range of cells, then we can achieve this by using a custom Data Validation formula.

Formula Syntax

=SUM($C$2:$C$9)<=50000

The Data Validation in Excel, requires us to set a custom formula based on the SUM function. This formula is to specify the amount users are limited to.

How to use the SUM Function Data Validation in Excel.

1.In our worksheet example below, we have a list of a breakdown of monthly budgetary expenses for an office.

Figure 2. Excel DATA VALIDATION using SUM Function.

Be sure to provide an empty cell for Excel to return the desired validation result.

2. Our purpose here is to limit users from entering a total monthly budgetary amount greater than or equal to $50,000.

The custom Data Validation formula we will enter into cell C2 of our worksheet example, is as follows;

=SUM($B$2:$B$8)<=50000

Figure 3. Excel DATA VALIDATION using SUM Function.

Our validation result was returned as FALSE meaning, the total of the amounts within the cell range B2:B8 is greater than our specified limit of $50,000.

Figure 4. Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.