Determine entries in a dropdown from a formula

Options
davidroy
davidroy
edited 12/09/19 in Formulas and Functions

Does anyone know if it is possible to select each item in a dropdown where you might have one or more entries, and separate each one in a formula to calculate hours * rate?

Or a work around if possible.

My current formula works fine for one entry as follows with the [Assigned To] column:

=SUMIFS({KCPL Hours _#OF HOURS}, {KCPL Hours _PROJECT_NUMBER}, [Task Name]$6, {KCPL Hours_PROJECT NAME}, [Task Name]$7, {KCPL Hours_PCL}, [Task Name]@row, {KCPL Hours_ASSIGNED TO}, [Assigned To]@row, {KCPL Hours_PROJECT TASK}, [Task Name]$12) * INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0))

The [Assigned To] column in some cases would contain two names.

Any help would be great.  Thanks ahead of time.

David

Capture 1.PNG

Capture 2.PNG

Comments

  • Alejandra
    Alejandra Employee
    Options

    Hi David,

    You may want to consider placing the contacts in separate rows. As you have found, the SUMIFS (and COUNTIFS) function doesn't recognize multiple contacts in a column.

    When you have a moment, please submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet. 

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!