How to COLLECT() a different reference value for each value in a multi-select column and SUM() them?

R.Whi

I have a cost matrix similar to this staged one:

The cost matrix is referenced in a second sheet of "buyer requests" to determine the cost of their request based on the Type, Size, and Vendor they chose:

When only ONE Type of Material is requested, the following formula works:

=INDEX(COLLECT({Cost}, {Vendor}, [Preferred Vendor]@row, {Size}, Size@row, {Type}, [Type of Material]@row), 1)

I need to be able to calculate the SUMMED cost if a request is made for MULTIPLE materials in the same purchase order, indicated by the multi-select Type of Material column.

Paul Newcome

Do you also need to accommodate multiple sizes within the same cell?

R.Whi

Hi @Paul Newcome, thank you for looking into my question!

Yes, ideally we would like to allow for multiple sizes, but we had been under the impression it was not possible. I would be very happy to be wrong!

Paul Newcome

How many different types of materials do you have, or what is the highest number of selections that can be made in a single cell for materials? Do you only have those three sizes?

It should be possible, but it will require helper columns to parse out the selections automatically.

R.Whi

@Paul Newcome, thanks for getting back to me!

There are 9 possible materials to select, and yes, our only sizes are Small, Medium, and Large.

Let me know if there is any additional information I can provide to give more context. I appreciate your time.

Paul Newcome

If they select multiple materials and multiple sizes, would all sizes match up to all materials or could it be something like medium wood and large steel?

R.Whi

@Paul Newcome, Medium Wood and Large Steel would be the goal.

Paul Newcome

What about large and small at the same time for the same material?

As of right now I am thinking separate "size" dropdown columns for each material for people to select from. That would certainly make things much easier. Multi-select dropdowns will always be in the same order, so if the size properties are

Small

Medium

Large

and Material properties are

Steel

Wood

then I would not be able to indicate that I want medium steel and small wood because the size selector would always put it as

Small

Medium

Do you think that is feasible? How are people currently filling out the sheet?

R.Whi

Hey @Paul Newcome,

Thanks for your quick responses!

No; one "material" can only have one size. Good question... This is very helpful.

We are currently ingesting the data through a form. The form fillers making the request do not see the sheet; only a couple of admin.

Just so I'm understanding, is this what you're thinking?

If so, this would definitely make things much easier in regard to the formula. We would just have the take of adding 9 columns to an already expansive sheet, but if it works, it works! If I'm following, is it possible to calculate the summed cost in one cell? Or will I require additional helper columns?

Paul Newcome

Yes. Just adding the 9 fields to the sheet should work. You can also leave in the Type of Material field as a multi-select dropdown and use form logic to only show the size field(s) for the material(s) selected in the form.

Then the base formula would be a SUMIFS. Write it out for each material (include an IF statement to help filter out errors from unselected materials) and add them together.

Base:

=IF([Wood Size]@row <> "", SUMIFS({$$ Column}, {Material Column}, @cell = "Wood", {Vendor Column}, @cell = [Preferred Vendor]@row, {Size Column}, @cell = [Wood Size]@row), 0)

Expanded for wood and steel to show syntax:

=IF([Wood Size]@row <> "", SUMIFS({$$ Column}, {Material Column}, @cell = "Wood", {Vendor Column}, @cell = [Preferred Vendor]@row, {Size Column}, @cell = [Wood Size]@row), 0) + IF([Steel Size]@row <> "", SUMIFS({$$ Column}, {Material Column}, @cell = "Steel", {Vendor Column}, @cell = [Preferred Vendor]@row, {Size Column}, @cell = [Steel Size]@row), 0) + ......................................................................

It does grow rather rapidly, but that is going to be the easiest way to manage it. Otherwise we would have to use helper columns to parse out the materials which can be a bit of a task in and of itself and then still need to figure out how to tell which size is for which material which would have to be manually entered somehow.