# SUMIFS Multiselect Dropdown

Creating an estimating sheet, that selects multiple people to a task. Each "role" has a different billable "rate"

Sheet #1: "DATA"

2 Columns: Roles and Billable Rates

~ 10 roles, changes per project

Sheet #2: "Select & Summary"

2 Columns:

Assignee: Multiselect drop down or each role. (would love a VLOOKUP for the dropdown...but a different ask)

Billable Rate: Would like it to sum the Roles selected for a combined hourly rate.

My attempt....

=SUMIFS({Rates Range}, {Roles Range}, CONTAINS(@CELL, INDEX([Assignee], MATCH(Assignee@row, {Roles Range},0))))

#UNPARSEABLE

• ✭✭✭✭✭✭

I think the formula you are looking for is:

=SUMIFS({Rates Range}, {Roles Range}, HAS(Assignee@row,@cell))

• ✭✭✭✭✭✭

Hi @Chris Hill

Hope you are fine, could you please supply a screenshot for your sheets ( remove any sensitive data ) to help me to write the exact formula for you

PMP Certified

[email protected]

www.mobilproject.it

• Thanks @Bassam.M Khalil

My intention was to have the =SUMIFS(.... in the "Auto \$/ Unit

• ✭✭✭✭✭✭

Hi @Chris Hill

you mean if you select for example Forman and he works for 15 hours and the unit rate for forman hour 2\$ you need the formula to check this rate from your Unit/Rate sheet and do the calculation and give you the result automatically?

bill = 15 * 2 = 30\$

PMP Certified

[email protected]

www.mobilproject.it

• sort of.

I want to be able to select:

Site Supervisor

Foreman

Apprentice

Then in Auto \$/ unit - it will show: \$195 - The sum of 75+65+55

Need to be able to select any combination and avoiding 13 - IF(CONTAINS)

• ✭✭✭✭✭✭
edited 02/09/21

Hi @Chris Hill

you can use the following formula and add all criteria with copy and paste with + and this you will do it for on time then convert the formula to column formula:

=COUNTIFS(W@row, CONTAINS("Site Supervisor", @cell)) * 75 + COUNTIFS(W@row, CONTAINS("Foreman", @cell)) * 65 + COUNTIFS(W@row, CONTAINS("Apprentice", @cell)) * 55

PMP Certified

[email protected]

www.mobilproject.it

• Thanks @Bassam.M Khalil I was trying to avoid that formula, because it is on roughly 400 lines in the sheet and the "roles" and "rate" can change by project.

I was trying to do something similar to @Paul Newcome solution from this thread....

Paul's solution with CONTIANS, + INDEX + MATCH should work....

The only difference is I do not have three sheets, just two. I think my problem is the range or use of INDEX/ MATCH.

Thanks for the help.

• ✭✭✭✭✭✭

I think the formula you are looking for is:

=SUMIFS({Rates Range}, {Roles Range}, HAS(Assignee@row,@cell))

• @Leibel S - THANK YOU. I knew there was a clean simple solution!!!!

Worked Perfectly!!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!