Sum if not duplicate

[rsomist]

I have a worksheet that consists of 6 columns. I am wanting to add up numbers from column E into column F, but only if it's not a duplicate line, as seen in column B. In the example I am attaching, lines 1 and 10 are duplicates, so that should not be included in the sum value. Thanks for any help!

 

[Chihiro]

Did you forget to attach your file?

At any rate, read link below for basic method/concept.
https://www.extendoffice.com/documents/excel/1705-excel-sum-average-unique-values.html

Although I prefer to use Pivot Table myself.

 

[rsomist]

Sorry, I guess it didn't save. Adding it now and I'll check out your link - thank you!

 

[rsomist]

That link addresses the unique value part, but only references 1 range that I can see. I need to compare two different columns and remove the duplicates from each of those two ranges.

 

[Thangavel]

Sorry, I guess it didn't save. Adding it now and I'll check out your link - thank you!

Hello,
column F is blank?!

 

[rsomist]

Right - that's where I would want the sum totals to go from column E, so the cells that I would need to enter in the function? So add up numbers from E with that total showing in F as a running total, assuming there's no duplicates (like row 10 in the attachment.)

Hello,
column F is blank?!

 

[Chihiro]

I'm confused by your sample sheet as well. What column(s) identify line as duplicate?

Column B is enough as you stated in OP? Or do you need to use some other column as well?

As well, isn't rows 11 to 15 duplicate as well? If it isn't, then what make them different from row 10?

 

[rsomist]

Ok, I think I oversimplified the example worksheet and just made it more confusing. Let me add another one and see if I can explain it a little better.

In column A, I have a list of ID's. There will be multiple duplicate ID's and then a new ID. At each change in ID, I need a new grand sum in column F. At the same time, I need any duplicated line numbers from column B (within the same ID number from column A) to not be included in the grand total in column F.

I have included the totals I am looking for the function to generate in column F. Here is one of the functions I have tried:

=SUMPRODUCT(--($A$21:$A$38=A21),$E$21:$E$38)
This function works to sum the totals with each change in ID number from column A, but does not address the duplicate line number problem.

Sorry for all of the confusion. Hope this helps clear some of it up.

 

[Chihiro]

Try this formula.
=SUM(IF(FREQUENCY(IF($A$2:$A$19=A2,MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)),ROW($E$2:$E$19)-1)>0,$E$2:$E$19))

Confirmed as array (CSE)

 

[rsomist]

That works - thanks so much!

 

[bosco_yip]

Another option (non array formula)

In H2, copied down :

=SUMPRODUCT(($A$2:$A$19=$A2)/COUNTIFS($A$2:$A$19,$A$2:$A$19,$B$2:$B$19,$B$2:$B$19),$E$2:$E$19)

Regards
Bosco