Can you sum all the distinct values in one column in Excel?

0
votes

I have list of Child Items, Parent items, and their associated costs. Some Child items are 2 or 3 levels deep and are part of multiple parent items. These child items also have children, and I need to sum up their cost to get a direct cost for each item. The problem is my dataset breaks everything out by the main parent item so that if I try to use something like someif(), my sum will be 2x or 3x too much because it counts all the children under each parent item.

For example

Parent_no  Child    Cost    Parent of Parent
1           a       2           b007
1           b       3           b007
1           a       2           c112
1           b       3           c112

So in my dataset above, Ideally when I write a formula the sum of the cost for parent_no 1 would be 5. Because it is also used as a child in a second item, whenever I try to write a formula based on Parent_no equaling 1 I get 10.

My only thought was maybe in the cost column I could put some sort of formula that would sum the first occurrence of a distinct value, cutting out my duplicate numbers. Any ideas? The whole dataset is some 12,000 items.

1
excelexcel-formulaworksheet-function
Parent_no is no use here since it is equal for all. So your formula will need to make use of either Child or Parent of Parent. The only column that matches your criteria is summing by Parent of Parent e.g. SUMIF(D:D,D2,C:C) – urdearboy
Yeah, I may be limited by the way the data table was set up originally. Using sumif Parent of Parent won't work either because the parent of parent will have multiple parent and child items connected to it also, and I'm also looking for the cost of the Parent_no items specifically. Every item is in that column. – Tyler Cohen
Will the cost always match the child ie.: All Child a is 2 and so on? – Scott Craner
please provide a larger sample data consisting as least 2 level of Parent Item, 3 level of Child Item, and the expected sum for each Parent Item. Please also confirm that Parent of Parent Item has no impact on the sum value at all, if not please clarify further. Thanks, – Terry W

1 Answers

1
votes

If the value on each child is always the same it is just a matter of summing the value of each entry by the number of times the child appears:

=SUMPRODUCT((C2:C5)*(A2:A5=E2)/(COUNTIFS(B2:B5,B2:B5,A2:A5,E2)+(A2:A5<>E2)))

enter image description here