# Get total cost based on multi select

Options
✭✭✭
edited 08/11/21

Hi everyone,

I have a total column that I want populated with a dollar amount. I want this to be dependant on the specific services selected from a multi select. The trick here is that the services rendered are completed by different vendors with different prices. So, the multi select prices will change based on the vendor. All of theses prices are in a price sheet.

On this same sheet (the total and multi select), the vendor is identified via index match.

I appreciate any feedback.

Tags:

• ✭✭✭✭✭✭
Options

Are you open to changing the layout of your vendor price sheet?

If you have it setup as 3 Columns (Example below) then the formula is a lot easier to put together.

Vendor, Service, Price

Vendor 1, Service 1, 450

Vendor 1, Service 2, 1500

...

...

The formula would be roughly:

=SUMIFS({prices}, {services}, HAS([Services]@row, @cell), {Vendors}, [Vendor]@row)

• ✭✭✭
Options

Thank you @Leibel Shuchat!

=SUMIFS({Subcontractor Price Sheet - Price}, {Subcontractor Price Sheet - Services}, HAS(Services@row, @cell), {Subcontractor Price Sheet - Subcontractors}, Subcontractor@row)

This works very well!

• ✭✭✭✭✭✭
Options

So could you have service 1 from vendor 1 and service 2 from vendor 2 on the same row, or would the different services in a single cell all come from the same vendor?

• ✭✭✭
Options

Excellent question. Thank you

The latter. Only one vendor is selected and billed for services.

Ex: Suppose Vendor 2 for a project completes services 1 and 3, but for a different project Vendor 3 is chosen and they are able to provide/complete all services.

• ✭✭✭✭✭✭
Options

What is the maximum number of services that could be selected in a single cell?

• ✭✭✭✭✭✭
Options

And how many different vendors do you have?

• ✭✭✭
Options

Currently only 4 services total and vendors are 5 max. It may increase but it's stable for now at 5 vendors.

• ✭✭✭✭✭✭
Options

Are you open to changing the layout of your vendor price sheet?

If you have it setup as 3 Columns (Example below) then the formula is a lot easier to put together.

Vendor, Service, Price

Vendor 1, Service 1, 450

Vendor 1, Service 2, 1500

...

...

The formula would be roughly:

=SUMIFS({prices}, {services}, HAS([Services]@row, @cell), {Vendors}, [Vendor]@row)

• ✭✭✭✭✭✭
Options

Another option (as discovered by the below post):

If you switch your services to the row and Vendor to the columns, you can do something like this:

=SUMIFS(INDEX({range of vendors and prices},0,MATCH([Vendor]@row, {Range of Vendors},0)),{range of services}, HAS([Services]@row, @cell))

• ✭✭✭