Calculating Data from Unique Values Only?

David Acord

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.

L_123

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)