SUM values of all entries' cost column matching a list of items

Question

Here's my sheet setup:

• I have a sheet "Inventory" that holds rows of all my inventory items in stock. Every item has a value/column for "Cost", and a column for "Sale Price".
• I have another sheet "Photography", that will have for every row/entry a cell which specifies which are the list of items used in the photo, referenced by some inventory item ref. And from that I want to vlookup or something, to fetch the "Cost" of all those items in the list, and SUM them up.
• That way I can know for a specific photo, what are all the items used in the photo, and how much is the cost of all those items (summed up).
• Then I will do the same for the "Sale Price", that way for every photo I take, I'll be able to tell the cost of that product item "as is" (with all the different inventory items), and the sale price.

Example:

I was able to do that for only 1 item, but I want the formula to work on the "list" of items in B9.

I even tried with this query formula, I just didn't know how to say "matching any item from the list", which will be a string in another cell:

=SUM(QUERY(A:B,"select B where A matches 'ID-100'"))

Answer 1

If your "items used" column consistently uses comma-space separated format for item lists, the following will work.

=query(Cost!A:B, "select sum(B) where A = '" & join("' or A = '", split(B9, ", ")) & "' label sum(B) ''")

Here, split splits the list of items used into individual item Ids. These are then joined by putting ' or A = ' in between, for example:

ID-101' or A = 'ID-101

There are bits prepended and appended to form the query string, e.g.,

select sum(B) where A = 'ID-101' or A = 'ID-101' label sum(B) ''

which does the job. The label part is necessary to get one-cell output, without a "Sum(Cost)" header getting in the way.

---

A detail concerning split: as written above, split(B9, ", ") splits by either comma or space (ignoring empty string in the output). So it will work even with ID-1,ID-2, ID-3 ID-4. On the other hand, this may be a problem if your IDs contain spaces. The stricter splitting mode is split(B9, ", ", False) which requires the whole string ", " to be used as a separator.