Inventory Formula Question

tworthington

I am trying to keep track of items we have to give out to community members. Mostly I am just making sure I know when I'm getting low on a certain item.

I made two sheets, one with the name of each unique item and one with more details that allows me to keep track of the person and date the item was given. Those details are more for my personal information. Here is the screenshot of the second sheet.

I am trying to use the row named Type to match the first sheet for a total of each one. I have the screenshot below. I was struggling to figure out what formula to use, and I have tried a few different ones with no luck. I also did a little googling to try and figure it out.

I was trying to see about using the total added column to count items that match the Item Name in the row. For example - I have 16 blankets listed on the second sheet. I would like it to see that 16 rows are labeled Blanket and put that number in the total added column. The removed column would subtract a number if the resident name has anything in the cell. Then the sum will be in the total remaining. It will help me know how many total items we've purchased as well as how many we've given out. I can also set up a reminder when I need to purchase more of an item.

Thank you for your help!

Paul Newcome

Make sure any column references in the same sheet as the formula are using the same column names that you have in your sheet. Try changing Type@row to [Item Name]@row.

KPH

Hi @tworthington

For the total added you can use a COUNTIF function - this will count the rows where a criteria is met, in our case if the Type matches the Type on the current row. The formula would look like this (depending on the name you give to your cross sheet references):

=COUNTIF({Inventory track Type}, Type@row)

For the removed column you can use COUNTIFS - this will count the rows where multiple criteria are met. In our case the type matches the type on the row and the resident name is not blank. The formula would look like this:

=COUNTIFS({Inventory track Resident}, <>"", {Inventory track Type}, Type@row)

I'll illustrate this. My "second sheet" looks like:

My cross sheet references are:

My final sheet looks like:

Let me know if you have any questions.

tworthington

I tried to follow your formula, but it is coming back as #UNPARSEABLE.

Here are the formulas I am using:

=COUNTIF({U&A Supply Additions Range 1}, Type@row)

=COUNTIFS({U&A Supply Additions Range 1}, <>"", {U&A Supply Additions Range 2}, Type@row)

The only thing I changed was the range by selecting "Reference another sheet"

Is this just not the correct way to get the information from the other sheet?

Paul Newcome

Make sure any column references in the same sheet as the formula are using the same column names that you have in your sheet. Try changing Type@row to [Item Name]@row.

tworthington

It helped to walk away and leave it for the day. I came back this morning and realized my mixup. It's working now. Thank you for your help!