Sum only the unique values from one column within a range of duplicate values from another column

[ma2169]

I am using Excel 365. I have looked and tried for hours but have been unable to come up with a good formula.

I have a worksheet with multiple columns. I need to write a formula (not a macro) in a 3rd column that sums only the unique values in column G but only for the rows that have duplicate values in column B. I have plenty of room for helper columns if needed. The formula should give one result for each unique ID number

I tried in an empty column T
=unique(B2:B1000)

then in Column U
=SUMIF(B2:B6,T2#,G2:G1000)

But this sums all instances where the ID is duplicated. I only want it to sum the unique values from columns G

So for example in the chart below.

The formula result for ID 18959 would be 4 as it would sum row 1 and row 3 because those are unique values within the duplicated range of ID 18959.
Similarly for ID 18960 the response would be 6 because that is the only unique value for that ID.

In my sheet ID is column A and Size is column G

ID	Size
18959	1.000
18959	1.000
18959	3.000
18959	3.000
18960	6.000
18960	6.000
18960	6.000

result should look like this

ID	Size
18959	4.000
18960	6.000

 

[Fluff]

How about

Fluff.xlsm
	A	B					G	H	I	J
1		ID					Size		ID	Size
2		18959					1		18959	4
3		18959					1		18960	6
4		18959					3
5		18959					3
6		18960					6
7		18960					6
8		18960					6
9
Sheet5

Cell Formulas
Range	Formula
I1:J3	I1	=LET(u,UNIQUE(FILTER(CHOOSECOLS(B1:G1000,1,-1),B1:B1000<>"")),GROUPBY(INDEX(u,,1),INDEX(u,,2),SUM,3,0))
Dynamic array formulas.

 

[alansidman]

Deleted. Mis read requirements

 

[Fluff]

That doesn't sum the unique values from the 2nd col, its adds them all.

 

[MARZIOTULLIO]

Book1
	A	B
1	ID	Size
2	18959	1
3	18959	1
4	18959	3
5	18959	3
6	18960	6
7	18960	6
8	18960	6
9
10	result should look like this
11
12	ID	Size
13	18959	4
14	18960	6
Sheet1

Cell Formulas
Range	Formula
B13:B14	B13	=IF(A13<>"",SUM(IF(FREQUENCY(IF($A$2:$A$8=A13,MATCH($A$2:$A$8&$B$2:$B$8,A$2:A$8&B$2:B$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),$B$2:$B$8)),"")

control +shift+enter

copy down

 

[ma2169]

How about

Fluff.xlsm
	A	B					G	H	I	J
1		ID					Size		ID	Size
2		18959					1		18959	4
3		18959					1		18960	6
4		18959					3
5		18959					3
6		18960					6
7		18960					6
8		18960					6
9
Sheet5

Cell Formulas
Range	Formula
I1:J3	I1	=LET(u,UNIQUE(FILTER(CHOOSECOLS(B1:G1000,1,-1),B1:B1000<>"")),GROUPBY(INDEX(u,,1),INDEX(u,,2),SUM,3,0))
Dynamic array formulas.

I pride myself on knowing excel but I have no clue what is going on here. Either way it works perfectly. Thank you so much! You have saved me so much aggravation.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[MARZIOTULLIO]

Glad we could help & thanks for the feedback.

Change your office version.
.