Sometimes you may want to limit the total amount in a column within a specific rage. I can give you several real-life examples of this specific use. So, in this tutorial, I am sharing with you how to limit a column total within a specific value. I hope it can be a new addition to your spreadsheet use in office or business.
Most of the companies pay their employees a fixed amount as allowances. It may be in the name of fuel expense, phone bill, meals expense etc. Even though the value is fixed, the company may want the bills for accounting purpose. In such cases, you can force your employees to submit their allowance bills in hard copy and the details in a spreadsheet.
You can share your eligible employees a standard spreadsheet format where they can enter their bill details. Additionally, in this sheet, you can restrict the total bill value to a fixed amount. See this example in detail under the below title.
How to Limit a Column Total Within a Specific Value
Here is the sample sheet.
In this example, I want the sum in cell D16 to be less than or equal to $500.00 as the employee is only entitled $500.00 as the monthly allowance in Cell C4. The sum range here is D7: D15. You can set a restriction to this range. So your employee can only enter bills until the total bill value reaches $500.00. We are depending on Google Sheets Data Validation in this case.
Here we are going to set a data validation rule in the range D7: D15 as below that would constantly monitor the entry in these cells and limit the entry sum within the specified limit.
Steps Involved:
First select the range D7: D15. Then set the validation rules as below from the Data Menu > Data Validation.
You should use the Dollar symbol in the custom formula. The purpose of this use is to make the range reference fixed.
That’s all. This way you can make sure that the SUM of the cells is not crossing your predefined value.
Conclusion:
The above is not only an example of how to limit a column total within a specific value but also an example to out of the box thinking of the dynamic use of the functions in Google Sheets. Hope you have liked the idea.
