Get total cost based on multi select

Reza Djangi

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.

DsCvpZ2cZP.png

FnnYPubqyw.png

Leibel S

@Reza Djangi

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)

Reza Djangi

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!

Paul Newcome

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?

Reza Djangi

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.

Paul Newcome

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

Paul Newcome

And how many different vendors do you have?

Reza Djangi

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

Leibel S

@Reza Djangi

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)

Leibel S

@Reza Djangi

Another option (as discovered by the below post):

https://community.smartsheet.com/discussion/82085/index-match-formula-has-me-stumped

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))

Reza Djangi

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!