Get total cost based on multi select

Reza Djangi
Reza Djangi ✭✭✭
edited 08/11/21 in Formulas and Functions

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:

Best Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @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
    Reza Djangi ✭✭✭
    Answer ✓

    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!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!