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.
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}))
CodePudding user response:
Try:
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))