I'm trying to create a tracking system using two sheets.

One sheet is the Gift Card Quantities has the number of existing gift cards in each dollar amount and the other is a tracker that allows the user to enter the gift card used, dollar amount, quantity of gift cards. What I want to do is if a $50 dollar gift card is used, then that number is subtracted from the existing number of gift cards on the Gift Card Quantities sheet. Don't know how to create a formula for this, any help would be greatly appreciated.

Find more posts tagged with

Formulas

inventory management

Accepted answers

Paul Newcome

Try this instead:

=[Number of cards]@row - SUMIFS({Other Sheet # of Cards Used Column}, {Other Sheet Dollar Maount Column}, @cell = [Dollar Amount]@row)

Then apply it as a column formula.

All comments

Paul Newcome

Are you able to provide screenshots with sample data for context?

CoBail

This is the tracking doc. Everytime someone uses a gift card they note it here. Two columns that need to be referenced are Dollar Amount and Number of Cards used

CoBail

This is the master sheet of total number of gift cards in each dollar amount. What I want to do is each time someone notes gift card use, the number of cards used is subtracted from the master sheet so we have a running current total of available gift cards in each dollar amount.

Paul Newcome

In that case you will need a column that has the Starting Number of Cards. Then you would use this:

=[Starting Card Count]@row - COUNTIFS({Source Sheet Amount Column}, @cell = [Dollar Amount]@row)

CoBail

Thank you! I got unparseable when I used that formula. I'm thinking it's because I also need to reference the dollar amounts in each sheet, so that if a $50 dollar gift is used vs a $150 dollar gift card it subtracts from the correct starting count row.

CoBail

Paul Newcome

You need to insert another column that has the starting total.

You also should only have a single set of curly brackets around your {Cross Sheet Reference}.

CoBail

The starting total is the "number of cards" column in the master sheet. Do I need to create an additonal column?

Paul Newcome

Yes. If the starting total is the column already existing in your screenshots, you will need to insert another column to house the formula.

CoBail

Thanks! That almost works, but I may have done something not quite right again. It's not subtracting from the tracking sheet.

CoBail

I changed the amount used in the tracking sheet for $50 dollar cards to 5 but it didn't subtract it from the master sheet, it still says 55 total instead of 50 total for $50 dollar cards

CoBail

CoBail

updated the brackets

Paul Newcome

Try this instead:

=[Number of cards]@row - SUMIFS({Other Sheet # of Cards Used Column}, {Other Sheet Dollar Maount Column}, @cell = [Dollar Amount]@row)

Then apply it as a column formula.

CoBail

Do i still need the additional column?

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.