How do I sum, excluding duplicate values?

[PattiK323]

Example:

I have one item number ABCDE but this item number appears 3 times, on rows 1 through 3. Each row lists a different raw material off the bill of material, along with the budgeted tons.

Material A 5 lb. 500 tons ABCDE
Material B 1 lb. 500 tons ABCDE
Material C 2lb. 500 tons ABCDE

This is repeated for hundreds of items. I want to verify how many tons are budgeted by summarizing item and tons.

Example:

ABCDE 500 tons [I only want the 500 included in the sum once, not the 3 times that it appears]
FGHIJ xxx tons
KLMNO xx tons

How do I do this?

 

[kweaver]

As a test:

[TABLE="width: 590"]
<colgroup><col><col span="5"><col><col span="2"></colgroup><tbody>[TR]
[TD]Material A[/TD]
[TD="align: right"]5[/TD]
[TD]lb.[/TD]
[TD="align: right"]500[/TD]
[TD]tons[/TD]
[TD]ABCDE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Material B[/TD]
[TD="align: right"]1[/TD]
[TD]lb.[/TD]
[TD="align: right"]500[/TD]
[TD]tons[/TD]
[TD]ABCDE[/TD]
[TD] [/TD]
[TD]ABCDE[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Material C[/TD]
[TD="align: right"]2[/TD]
[TD]lb.[/TD]
[TD="align: right"]500[/TD]
[TD]tons[/TD]
[TD]ABCDE[/TD]
[TD] [/TD]
[TD]FGHIJ[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]Material D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]700[/TD]
[TD] [/TD]
[TD]FGHIJ[/TD]
[TD] [/TD]
[TD]KLMNO[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Material E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]250[/TD]
[TD] [/TD]
[TD]KLMNO[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Material F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]250[/TD]
[TD] [/TD]
[TD]KLMNO[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Material G[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]700[/TD]
[TD] [/TD]
[TD]FGHIJ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Would this work in I2:I7 or further down rather than just 7? My column I is where the total amounts are (I had a blank G column)

=INDEX($D:$D,MATCH(H2,$F:$F,0))

 

[PattiK323]

What is your column D and F?

 

[kweaver]

D has the tons (e.g., 500, 500, 500, 700, 250, 250, 700) -- are they always the same for a given ITEM?

My F is ABCDE, etc.

MATCH will find the first match being equal to the ABCDE, etc.

Change the Index/Match accordingly based on your set-up.

 

[Twollaston]

Here with column and row names, i shared the formula you already received and an alternative formula. Consider changing your source data to a table
and then you can create a unique list from column F using a 1 row pivot table(this will sum the amount for you too) , and run your formulas from that. So you don't have to update it
also you can add =iferror(yourformula,"") to get rid of the errors so you can extend the list down

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Material A	5	lb.	500	tons	ABCDE	500	500	ABCDE	500	500		Sums	1450	#N/A
2	Material B	1	lb.	500	tons	ABCDE	500	500	FGHIJ	700	700
3	Material C	2	lb.	500	tons	ABCDE	500	500	KLMNO	250	250
4	Material D			700	tons	FGHIJ	700	700			#N/A
5	Material E			250	tons	KLMNO	250	250			#N/A
6	Material F			250	tons	KLMNO	250	250			#N/A
7	Material G			700	tons	FGHIJ	700	700			#N/A
Sheet6

Cell Formulas
Range	Formula
G1	=SUMIF($F$1:F7,F1,$D$1:D7)/COUNTIF($F$1:$F$7,F1)
H1	=INDEX($D:$D,MATCH(F1,$F:$F,0))
J1	=IFERROR(SUMIF($F$1:F7,I1,$D$1:D7)/COUNTIF($F$1:$F$7,I1),"")
K1	=INDEX($D:$D,MATCH(I1,$F:$F,0))
N1	=SUM(J1:J7)
O1	=SUM(K1:K7)

 

[kweaver]

Hey Twollaston...How do you paste that image into the body of the reply with the row/column indicators?

Kevin

 

[Twollaston]

Hey Twollaston...How do you paste that image into the body of the reply with the row/column indicators?

Kevin

I downloaded the(2017) html maker from here

https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813

Extract file to excel add ins folder (you can find path by going to excel on developer tab by selecting excel addins and selecting browse- copy path, paste as extraction path)

Check(on your extracted file) properties -> General and make sure it’s not blocked at the bottom

Enable htmlmaker in excel addins(developer tab -> excel addins-> enable checkbox)

Set excel addins folder path as trusted in trust settings

Open htmlmaker tab in excel

Highlight the excel cells you want

Select type of formula display (I use show first column in each row)

It will copy the html display code to your clipboard and just paste it into your reply on the forum.

If you need any help doing this feel free to PM me and I’m happy to further explain