How do I sum, excluding duplicate values?

PattiK323

New Member
Joined
Jun 12, 2014
Messages
35
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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,043
Office Version
365, 2010
As a test:

Material A5lb.500tonsABCDE
Material B1lb.500tonsABCDE ABCDE500
Material C2lb.500tonsABCDE FGHIJ700
Material D 700 FGHIJ KLMNO250
Material E 250 KLMNO
Material F 250 KLMNO
Material G 700 FGHIJ

<colgroup><col><col span="5"><col><col span="2"></colgroup><tbody>
</tbody>

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)

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

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,043
Office Version
365, 2010
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.
 
Last edited:

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
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



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Material A</td><td style="text-align: right;;">5</td><td style=";">lb.</td><td style="text-align: right;;">500</td><td style=";">tons</td><td style=";">ABCDE</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style=";">ABCDE</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style=";">Sums</td><td style="text-align: right;;">1450</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Material B</td><td style="text-align: right;;">1</td><td style=";">lb.</td><td style="text-align: right;;">500</td><td style=";">tons</td><td style=";">ABCDE</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style=";">FGHIJ</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Material C</td><td style="text-align: right;;">2</td><td style=";">lb.</td><td style="text-align: right;;">500</td><td style=";">tons</td><td style=";">ABCDE</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style=";">KLMNO</td><td style="text-align: right;;">250</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Material D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">700</td><td style=";">tons</td><td style=";">FGHIJ</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Material E</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td><td style=";">tons</td><td style=";">KLMNO</td><td style="text-align: right;;">250</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Material F</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td><td style=";">tons</td><td style=";">KLMNO</td><td style="text-align: right;;">250</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Material G</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">700</td><td style=";">tons</td><td style=";">FGHIJ</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G1</th><td style="text-align:left">=SUMIF(<font color="Blue">$F$1:F7,F1,$D$1:D7</font>)/COUNTIF(<font color="Blue">$F$1:$F$7,F1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">=INDEX(<font color="Blue">$D:$D,MATCH(<font color="Red">F1,$F:$F,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J1</th><td style="text-align:left">=IFERROR(<font color="Blue">SUMIF(<font color="Red">$F$1:F7,I1,$D$1:D7</font>)/COUNTIF(<font color="Red">$F$1:$F$7,I1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K1</th><td style="text-align:left">=INDEX(<font color="Blue">$D:$D,MATCH(<font color="Red">I1,$F:$F,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N1</th><td style="text-align:left">=SUM(<font color="Blue">J1:J7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O1</th><td style="text-align:left">=SUM(<font color="Blue">K1:K7</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,043
Office Version
365, 2010
Hey Twollaston...How do you paste that image into the body of the reply with the row/column indicators?

Kevin
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,274
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top