Help with a formula between two sheets

BerryC

I have one sheet that pulls information from a form. I have a cell on (Asset Tracker) that has a value of the requested items. On another sheet (Uniform Inventory) I want to count the total number of items requested on a continuing basis. I've written this formula, but I don't think it's correct. I get an uparsable error.

=IF({Sheet - Asset Tracker Range 2}, CONTAINS(Woman Summer shirt-XS-Black, COUNT({Sheet - Asset Tracker Range 3})

Jennifer Kurtz

@BerryC

Thanks for the additional details!

Is the item ("Woman Summer shirt-XS-black") always referenced/formatted in the same way on your Asset Tracker sheet? That will be important, because you're referencing that item in the formula. The formula is looking for rows that contain exactly the text in quotes.

When I typed the formula, I think I included an extra space between XS and -black which was not in your original post! Perhaps that's the problem?

One other note: if your Inventory Sheet lists out each item (e.g, a column named "Item" and you're calculating the total issued of that item in the next column, you can save yourself some typing and reference the "Item" in the row. It would look like this:

=SUMIF({Sheet - Asset Tracker Range 2}, CONTAINS(Item@row, @cell), {Sheet - Asset Tracker Range 3})

Then you can use that formula for the entire column.

Does this make sense? Formulas are super fun and powerful --but they take a little bit of getting used to! I think you're really really close!

Jennifer Kurtz

Hi!

Depending on how you're tracking this information, I think the COUNTIF or SUMIF function might do the trick.

It looks like you have a column for the type of item that you're referencing in the {Sheet - Asset Tracker Range 2} above. What is the {Sheet - Asset Tracker Range 3} that you're counting?

COUNTIF will count the number of cells in a range that meet all the criteria you specify. SUMIF will total numbers within a range that meet a criteria you specify. (SUMIFS & COUNTIFS are great when you have more than one criteria.)

If you want to simply count all the cells within your "range 2" that contain the shirt, the syntax is =COUNTIF(Range, criterion). It would look like this:

=COUNTIF({Sheet - Asset Tracker Range 2}, CONTAINS("Woman Summer shirt-XS-Black", @cell)

If you have a number of the item that you'd like to total - maybe in your range 3? The syntax is =SUMIF(Range, criterion, Sum Range if different from Range), Thaat could look like this:

=SUMIF({Sheet - Asset Tracker Range 2}, CONTAINS("Woman Summer shirt-XS-Black", @cell), {range you want to sum}

Don't know if that helps - feel free to post a screenshot or provide a little more detail about your ranges. :)

BerryC

Jennifer,

Thank you for the help. The SUMIF you provided gets me close. Here is the formula, but it does not provide a total, just zero.

=SUMIF({Sheet - Asset Tracker Range 2}, CONTAINS("Woman Summer shirt-XS -Black", @cell), {Sheet - Asset Tracker Range 3})

On my Uniform Inventory Sheet, I have a column with current inventory that is hand-keyed. The formula you helped me create is in the Items Issued column. The information for the Issued column is coming from my Asset Tracker sheet. I have a column title size and style of summer shirt issued. It is this column that has the

Woman Summer shirt-XS -Black. Based on that criteria being met, I then want to pull and count the number in the column next to it titled Summer Uniform Shirt, which is the total issued to each employee. In the formula on my Uniform Inventory sheet, in the items issued column, I'd like the total from the asset tracker when the specific criteria are met to keep a running count in that column.

I really appreciate the help. This is all new to me.

Unknown

This content has been removed.

Jennifer Kurtz

@BerryC

Thanks for the additional details!

Is the item ("Woman Summer shirt-XS-black") always referenced/formatted in the same way on your Asset Tracker sheet? That will be important, because you're referencing that item in the formula. The formula is looking for rows that contain exactly the text in quotes.

When I typed the formula, I think I included an extra space between XS and -black which was not in your original post! Perhaps that's the problem?

One other note: if your Inventory Sheet lists out each item (e.g, a column named "Item" and you're calculating the total issued of that item in the next column, you can save yourself some typing and reference the "Item" in the row. It would look like this:

=SUMIF({Sheet - Asset Tracker Range 2}, CONTAINS(Item@row, @cell), {Sheet - Asset Tracker Range 3})

Then you can use that formula for the entire column.

Does this make sense? Formulas are super fun and powerful --but they take a little bit of getting used to! I think you're really really close!

BerryC

Jennifer,

Thank you for your help. I'm new to this and am struggling. It looks like the formula did that trick. Thank you for your help. I appreciate your time!

Jennifer Kurtz

awesome - so glad you got it working!