Formula needed

jsmith1872

I am trying to set up a formula that will subtract from a given value, each time that item is found.

I have a form that my team will be using to enter data (item) and how much they found (subtract from value).  In turn, I need to have a running total of what is still not reported for each item.

 

Schiff A.

Hi jsmith1872,

To accomplish this task, you can take advantage of the COLLECT() function which is used within another function to collect specific values in a range that meet the provided criteria.

 

Essentially we're going to "collect" the total number of time number of times an item is found and subtract that from the initial value.

Here's an example of how you can set this up:

1. Create a column called "Item" (this is the label that identifies what someone finds)
2. Create a column called "Number" (this is the amount of that item they found)
3. Create a column called "Value" and enter the following formula: =X - SUM(COLLECT(Number:Number, Item:Item, "Label")) where X is the initial value and Label is the name of that item

Depending on exactly how your sheet is set up you may need to vary this approach. You can find more information about the COLLECT() function here: https://help.smartsheet.com/function/collect

Thanks!

Schiff

​​​​​​​Smartsheet Support