Smartsheet summary formula

Josephine

Hi All

I am trying to create a smartsheet formula for the Summary field that will return the maximum someone in a Senior Quantity Surveyor role is being paid currently in the company, plus the name of the person. I have the max salary part sorted -

=MAX(COLLECT(Salary:Salary, [Job Title]:[Job Title], "Senior Quantity Surveyor", [Contract Type]:[Contract Type], "<>Permanent (P/T)", [Current status]:[Current status], "Active"))

But cannot figure out how to include the person's name as well, potentially more than one person could earn the current maximum so it would be trying to return all names of SQS who earn the maximum.

i.e Highest someone earns in that role is $250,000 so I would have a result of "$250,000, Bob Jones"

Because I am wanting numbers and letters to be returned I would need to include formatting for the salary part since I cannot format the answer as currency?

The reason I am trying to do this as I have a dashboard created from the summary fields that shows what the minimum and maxiumum in each role is currently being earnt, it would be helpful to have a name attached to the dollar amounts. If there is a different way I can get the same result I am open to it!

Thanks.

Michelle Choate 2

So you would have one field that says SALARY with your formula:

=MAX(COLLECT(Salary:Salary, [Job Title]:[Job Title], "Senior Quantity Surveyor", [Contract Type]:[Contract Type], "<>Permanent (P/T)", [Current status]:[Current status], "Active"))

Then you would have a formula that is an JOIN/COLLECT

= JOIN(COLLECT(Name:Name, Salary:Salary, [SALARY]#), ", ") + ", " + Salary#

This would provide the names + the salary at the end. If you want to switch the pieces of the formula up, do.

Michelle Choate 2

@Josephine - I originally made a comment saying that this was not possible thru the Sheet Summary to format this way. And then I stopped and thought about it. Because we are using a formula, we can just update the formula! Use this formula:

= JOIN(COLLECT(Name:Name, Salary:Salary, [SALARY]#), ", ") + ", $" + Salary#

Michelle Choate 2

So you would have one field that says SALARY with your formula:

=MAX(COLLECT(Salary:Salary, [Job Title]:[Job Title], "Senior Quantity Surveyor", [Contract Type]:[Contract Type], "<>Permanent (P/T)", [Current status]:[Current status], "Active"))

Then you would have a formula that is an JOIN/COLLECT

= JOIN(COLLECT(Name:Name, Salary:Salary, [SALARY]#), ", ") + ", " + Salary#

This would provide the names + the salary at the end. If you want to switch the pieces of the formula up, do.

Josephine

Hi @Michelle Choate 2

Thanks for responding! That is what I needed. I am now getting the response I was after -

Bob Jones, 250000

Is there anyway to format the dollar amount?

Michelle Choate 2

@Josephine - I originally made a comment saying that this was not possible thru the Sheet Summary to format this way. And then I stopped and thought about it. Because we are using a formula, we can just update the formula! Use this formula:

= JOIN(COLLECT(Name:Name, Salary:Salary, [SALARY]#), ", ") + ", $" + Salary#