SUMIFS Multiselect Dropdown

Chris Hill

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

Leibel S

@Chris Hill

I think the formula you are looking for is:

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

Bassam Khalil

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

Chris Hill

Chris Hill

Thanks @Bassam.M Khalil

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

Bassam Khalil

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$

Chris Hill

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)

Bassam Khalil

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

Chris Hill

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

https://community.smartsheet.com/discussion/66066/how-to-use-multi-select-dropdown-list-as-criteria-for-sumif-vlookup

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.

Leibel S

@Chris Hill

I think the formula you are looking for is:

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

Chris Hill

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

Worked Perfectly!!!