Sometimes, you may need to limit the total amount in a column to stay within a specific range. There are several real-life scenarios where this can be useful. In this tutorial, I’ll show you how to use data validation in Google Sheets to limit a column total to a specific value.
Many companies pay their employees a fixed amount in allowances, such as fuel expenses, phone bills, or meal expenses. Even though the amount is fixed, companies often require employees to submit their bills for accounting purposes.
In such cases, you can ask employees to submit their allowance bills in hard copy and also provide the details in a spreadsheet. You can share a standardized spreadsheet format where employees can enter their bill details. Additionally, you can restrict the total bill amount to a fixed limit within the sheet. See a detailed example of this under the title below.
How to Limit a Column Total to a Specific Value
In this example, we will limit the total amount in the range D7:D15 to fall within a specific limit.
I want the sum formula in cell D16, =SUM(D7:D15), to return a value that is less than or equal to $500.00, as the employee is entitled to a maximum of $500.00 for the monthly allowance, which is entered in cell C4.
The sum range here is D7:D15. You can set a restriction on this range so that the employee can only enter bills until the total reaches $500.00. We will achieve this using Google Sheets’ Data Validation.
By setting a data validation rule on the range D7:D15, we can continuously monitor the entries in these cells and ensure the total stays within the specified limit.
Steps Involved:
- First, select the range D7:D15.
- Click on Data > Data Validation.
- In the “Data validation rules” panel that opens, click Add rule.
- Under Criteria, select Custom formula is.
- Enter the following formula in the provided field:
=SUM($D$7:$D$15)<=$C$4 - Check the Reject input option under Advanced options.
- Click Done.
That’s it! This ensures that the sum of the cells does not exceed your predefined limit.
Resources
Here are some related resources for Google Sheets.
