Go Back

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.

  1. 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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I don't know how to condense this formula as there is a limit of characters for one cell and it seems I exceed it.
Solved by A. Q. in 28 mins
I'd like a formula to sum n number of values, that don't appear in a range!
Solved by B. F. in 21 mins
I need C30:C35 not to be able to exceed the figures adjacent row/column G30:G35. 5% 10% 10% 5% 5% I also need the total sum in column C30:C35 to not be able to exceed 20% while only allowing for a maximum of 3 inputs into C30:C35 if possible.
Solved by G. C. in 18 mins

Leave a Comment

avatar