Interacting with multiple entries in data validation

[sarahnocquet]

Hi, I am trying to make an inventory spreadsheet for my bag business. Each bag is made out of 1-4 used pairs of pants. I often use a same pair for several different bags, and often use several pairs for one bag. For each row where I log a bag, I want to have a drop-down menu where I can select which pants were used to make the bag. I found some code online that allows me to enter several items from the drop-down menu with commas, but this becomes an issue when I try the COUNTIF formula to see how many bags each pair of pants contributed to (the formula only recognizes that pants were used if the entry is alone in the cell, not separated by a comma with other entries). My goal is to be able to figure out the materials cost for each bag (i.e., the cost of the pants used, where each price of pants is divided by the number of bags it is used in). Does anyone have any tips or ideas on how to do this?

[#2]

@sarahnocquet 

COUNTIF supports wildcards, so instead of (for example)

=COUNTIF(A2:A100, "chinos")

you can use

=COUNTIF(A2:A100, "*chinos*")

The latter formula will count all cells in A2:A100 that contain the text chinos together with possibly other text.

[#3]

@sarahnocquet , Based on your description, I mocked a spreadsheet that calculates the cost of bag based on the price of pants and how they are used. You can add a bag to the bottom of the left table and select the pants used to see the calculated cost change for all bags. Let me know if this is what you are envisioning.