Formula Help

therapy_mindset

Hello,

I'm trying to create an inventory of all the items we currently have in our uniform room.

The first column will have POLO Shirts USED through the year, the second column I would like to have our current stock inventory inside the room.. the third column will indicate if it was either provided to a staff member or put in the inventory room. .. if provided to a staff member the second column (POLO Shirts currently) should go down. If inventory room the first column should increase.

Alan P.

Will this information be entered via a form, and top loaded into the the grid?

Genevieve P.

Hi @therapy_mindset

It sounds like you want to first do a SUMIF adding together all the totals IF the row is associated with "inventory Room", then from this total, subtract the SUMIF that adds all the totals IF the row is associated with "To Staff".

See - SUMIF Function

Ex:

=SUMIF([To Inventory Room vs To Staff]:[To Inventory Room vs To Staff], "Inventory Room", [Formulas - POLO Shirts USED]:[Formulas - POLO Shirts USED]) - SUMIF([To Inventory Room vs To Staff]:[To Inventory Room vs To Staff], "To Staff", [Formulas - POLO Shirts USED]:[Formulas - POLO Shirts USED])

Let me know if this is what you were looking to do!

Cheers,

Genevieve

therapy_mindset

Hi @Genevieve P.

Thank you for your response... i also forgot to include sizes for each contract... There are currently 5, each contract has a different shirt...

I would like to know how many shirts contain in each contract by size.

Genevieve P.

HI @therapy_mindset

Where are you creating this calculation, will this be on the same sheet or a different sheet? It sounds like you may want to set up a different sheet with a table for your formula.

In this case, you would have one row for each size and three columns: one for "To Staff", one for "Inventory Room", and one for the subtraction/total.

This way you can SUM the "Polo Small" column IF the "IO" is "Inventory Room" in the first column:

=SUMIF({IO Column}, "Inventory Room", {Polo Small Column})

In the second column you'd SUM the values only if they're "To Staff"

=SUMIF({IO Column}, "To Staff", {Polo Small Column})

Then you can have a third column that subtracts one value from the other.

In the next row you would do the same thing but for "Polo Med", and you'd need to replace out the column reference to look at the right column to SUM:

=SUMIF({IO Column}, "Inventory Room", {Polo Med Column})

Let me know if this makes sense and works for you!

Cheers,

Genevieve