8 Replies Latest reply on Mar 14, 2018 5:37 AM by Zhouyi Zhang

    Dividing one value by the sum of unique values

    Tom Williams

      Hi all


      I have a dataset in which I am tracking the ownership of some household objects. Some objects are owned by more than one person so they have been entered in multiple rows into the dataset with one row for each person (including a field on the percentage of the object they own).


      What I am trying to do is create a calculation so I can show of the total value of the household objects, how much of the value are the owners linked to.


      For example:


      John is involved in the ownership of a $1500 PC and a $500 TV but not in the $2000 dining suite. The total value of the items is $4000 and John is involved in the ownership of $2000 worth of those goods so he has a 50% stake in the total value.


      I've tried creating a calculated field to do this calculation which I thought would be relatively simple: [value]/sum([value]). However I can't get that formula to work and I need to sum of the entire column to only count each item once. For example if the Dining Suite which is valued at $2000 is owned by four people, it is entered into the sheet four times so when I sum the value it comes at $8000 instead of $2000.


      However I can't seem to get it to get it to work unfortunately. I've attached a packaged workbook which has a subset of the data.