How to Sum Distinct Values and Create a Metric Sheet

Richard.Herrera

Here is some context:

We have team members who conduct group activities with clients within the community. Participants of the group are asked to complete a survey. The team members' leadership created formulas within the survey to calculate a "unit of credit," which will be summed throughout the year and used for annual goal performance.

The issue is that since multiple surveys can be received, SUMIF function totals all the rows; leadership only wants to sum one distinct "unit" for each team member. Additionally, the Team Member column is a multi-select column, complicating the formula.

Here is a snippet of the source sheet:

In "human-speaking" terms, the formula would work like this:

If the team member's name is in the Team Member Name column, SUM the AG8 Unit of the unique/distinct event title and date of service.

In the above screenshot, there are a total of 6 unique instances of event title and date of service:

Encuesta de satisfaction: Tu Voz Cuenta 2023 Spanish has 5 different dates of service, and Middle School Bullying Presentation has one service date. Therefore, if I sum the unique instances for Adriana, she received 12.75 units for the six group activities. Arleen would total 1.5 units for also participating in the Middle School Bullying Presentation.

We've tried a few things, such as

=COUNTIF(DISTINCT((COLLECT({Survey Date}, {Survey Date}, ISDATE(@cell)))), {Event Title} = [Event Title]@row)

=COUNTIF(DISTINCT({Survey Date}), [Event Title]@row = {Event Title})

=IFERROR(INDEX(COLLECT......

and can't seem to get any progress

Any help is greatly appreciated! Thank you in advance to the Smartsheet Gurus!

jmyzk_cloudsmart_jp

@Richard.Herrera

You can use the RANKEQ() function to determine a unique "Event Type"-"Date of Service" combination if you can add a unique number column made up of unique values like Row Id or Created (date). You can use the following RANKEQ formula:

• =RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Event Title]:[Event Title], [Event Title]@row, [Date of Service]:[Date of Service], [Date of Service]@row), 1)

Once you have the RANKEQ values, you can use the SUMIFS function to calculate the sum of values in another sheet (let's call it "Survey Sheet Range AG8 Unit") where the RANKEQ value is equal to 1 and other conditions are met. Here's an example formula:

• =SUMIFS({Survey Sheet Range AG8 Unit}, {Survey Sheet Range Team Member Name}, HAS(@cell, [Team Member Name]@row), {Survey Sheet Range RANKEQ}, 1)

Please take a look at the demo Dashboard below.

https://app.smartsheet.com/b/publish?EQBCT=d7f5eaa6ec5549a7927f53054c294e73

jmyzk_cloudsmart_jp

@Richard.Herrera

You can use the RANKEQ() function to determine a unique "Event Type"-"Date of Service" combination if you can add a unique number column made up of unique values like Row Id or Created (date). You can use the following RANKEQ formula:

• =RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Event Title]:[Event Title], [Event Title]@row, [Date of Service]:[Date of Service], [Date of Service]@row), 1)

Once you have the RANKEQ values, you can use the SUMIFS function to calculate the sum of values in another sheet (let's call it "Survey Sheet Range AG8 Unit") where the RANKEQ value is equal to 1 and other conditions are met. Here's an example formula:

• =SUMIFS({Survey Sheet Range AG8 Unit}, {Survey Sheet Range Team Member Name}, HAS(@cell, [Team Member Name]@row), {Survey Sheet Range RANKEQ}, 1)

Please take a look at the demo Dashboard below.

https://app.smartsheet.com/b/publish?EQBCT=d7f5eaa6ec5549a7927f53054c294e73

Richard.Herrera

Amazing, @jmyzk_cloudsmart_jp !!!

Your solution worked swimmingly!!! I was not aware of RANKEQ. Words cannot express our gratitude for the time taken to help us.

Warm regards,

Richard

jmyzk_cloudsmart_jp

@Richard.Herrera

Glad it worked, Richard! Happy to help anytime. Keep up the good work! 😊

Juni