How to Limit a Column Total to a Specific Value in Google Sheets

Published on

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.

Example of limiting column total in Google Sheets using data validation

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:

Data validation settings for limiting the sum in a range in Google Sheets
  • 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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.