Get total cost based on multi select
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.
Best Answers
-
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)
-
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
-
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?
-
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.
-
What is the maximum number of services that could be selected in a single cell?
-
And how many different vendors do you have?
-
Currently only 4 services total and vendors are 5 max. It may increase but it's stable for now at 5 vendors.
-
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)
-
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))
-
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!
Help Article Resources
Categories
Check out the Formula Handbook template!
