How to subtract quantities from one sheet via forms to another sheet with total quantities

My issue is this:

I have a master sheet with material types (A1,A2,A3 etc..) and total material quantities (#'s). The other sheet I am using is updated via forms. The form is used for distributing materials and requires a material type, and material quantity before submission.

Once submitted I would like the quantity to be subtracted from my "total material quantities" from my master sheet. The issue is that there are multiple material types with the names, "A1","A2","A3" etc....

So lets say I have two form submissions that have updated my forms sheet. One submission is for 5 "A1" materials. Another submission is for 3 "A2" materials. What function can I use to locate "A1" or "A2" material quantities and have them subtracted from my master sheet.

All help is much appreciated😀

Find more posts tagged with

Forms

Formulas

functionality

Automations

Cell linking

Accepted answers

SolutionSal

Hello, sounds like you're in need of a SUMIF formula.

Your master sheet could have a [Quantity Used] column with the formula written below. You'll need to create cross sheet references to your 'material request' form sheet. One for the material type column and another for the quantity.

=SUMIF( {material type}, "A1", {quantity} )

This could then be used to calculate a [Current Quantity] column with a formula like so

= [Start Quantity]@row - [Quantity Used]@row

Hope this points you in the right direction!

All comments

SolutionSal

Hello, sounds like you're in need of a SUMIF formula.

=SUMIF( {material type}, "A1", {quantity} )

This could then be used to calculate a [Current Quantity] column with a formula like so

= [Start Quantity]@row - [Quantity Used]@row

Hope this points you in the right direction!

phildyche

Thank you so much! this solved my issue

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.