Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Calculating Data from Unique Values Only?

    ✭✭
    edited 03/18/22 in Formulas and Functions

    Hi all!

    Here's the ask.. I'm not sure if it can be done, and if so, I have no idea how to do it! Help?


    Ask: Calculate the Total Contract Value (TCV) when the company is unique. Disregard multiple requests from the same company.


    If you look at the example here, you can see a few companies that have submitted multiple requests (three from Company A, three from Company B), each with a Total Contract Value (TCV) next to it. (ignore the fact that some of them were entered in with different amounts..)


    What I'm trying to do is get a sum of the TCV, but I don't want to count company A three times, or company B three times. It should count one instance of Company A, and one instance of Company B though. Is there a way to use a formula to calculate this? I'm guessing it requires DISTINCT but I don't know how to go about working it out... I'm happy to use whatever helper columns are needed because ultimately I'm hoping to use the formula in a summary field so I can put it on a dashboard elsewhere.


    image.png


    · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      Use a helper column to identify the columns you want to count, then use a sumif based on the helper column. Instead of distinct, use a countif with an absolute reference to grab the desired submission from the company.

      example:

      =if(Countif([Customer Name]$1:[Customer Name]1,[Customer Name]@row)>1,0,1)

      Then drag down. 1 will be posted on the first submission of the company, 0 for all later submissions.

      If you want to grab the latest submission, you can do something similar with a created column and the formula below in the helper column:

      =if(created@row = max(collect(created:created,[Customer Name]:[Customer Name],[Customer Name]@row),1,0)

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions