Calculating Data from Unique Values Only?

Options
David Acord
David Acord ✭✭
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.



Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!