Home > Enterprise >  Excel: How to add unique values based on another column but not unique when on its own
Excel: How to add unique values based on another column but not unique when on its own

Time:08-02

Is there way to achieve the SUM() of all the unique values in the WEIGHT column, based on the unique value from VISIT ID column, and only add those that repeat only if the VISIT ID value changes?

So control-break on VISIT ID and add unique values per VISIT ID. I hope that made sense? Thanks

The SUM() here is only for illustration of what I am trying to achieve.

enter image description here

Data:

VISIT ID    VESSEL NAME  WEIGHT 
15  FLORA DELMAS     2,740.9580 
15  FLORA DELMAS     2,740.9580 
15  FLORA DELMAS     2,740.9580 
15  FLORA DELMAS     2,740.9580 
15  FLORA DELMAS     2,591.1002 
15  FLORA DELMAS     2,591.1002 
15  FLORA DELMAS     2,591.1002 
15  FLORA DELMAS     578.7480 
15  FLORA DELMAS     124.9010 
15  FLORA DELMAS     19,994.0000 
20  SOUTHGATE    19,994.0000 
20  SOUTHGATE    19,994.0000 
20  SOUTHGATE    11,013.0000 
22  CAMILA   11,013.0000 
22  CAMILA   28,521.0000 
32  GULF LIVESTOCK 1     3,696.0000 
32  GULF LIVESTOCK 1     3,696.0000 
34  GOLDEN YOSA  2,905.0000 

UPDATE 1

I forgot to say that I want the formula to use the range by columns and not across several adjacent columns... ie: A2:A19 and C2:C19... as the unique identifiers. Reason is I have many columns actually between A and C that are not in this sample, and I don't want to span all of them as they could change for other reasons.

CodePudding user response:

Give a try on-

=SUM(FILTER(UNIQUE(FILTER(A2:C19,{1,0,1})),{0,1}))

enter image description here

CodePudding user response:

Try:

enter image description here

Formula in F1:

=SUM(INDEX(UNIQUE(A2:C19,0),0,3))

Or, specifically VISIT ID and WEIGHT:

=SUM(INDEX(UNIQUE(CHOOSE({1,2},A2:A19,C2:C19)),0,2))
  • Related