Capture Unique Record Once And Sum

[exceleratedgeek]

I have a data set with repeating data. I need to sort the duplication and capture the value only once. I came up with a countif formula that works for counting number of unique records but i can't figure out how sum the unique value. Below is a sample of the data set.

SHIPMENT_NO AMOUNT
SPA0001 $629,800.00
SPA0001 $629,800.00
SPA0002 $542,100.00
SPA0002 $542,100.00
SPA0002 $3,149,000.00
SPA0004 $629,800.00
SPA0003 $2,519,200.00
SPA0005 $1,259,600.00
SPA0006 $629,800.00
SPA0007 $1,259,600.00
SPA0008 $2,168,400.00
SPA0009 $542,100.00
SPA0022 $629,800.00
SPA0022 $542,100.00
SPA0023 $1,259,600.00
SPA0024 $629,800.00
SPA0025 $629,800.00
SPA0024 $542,100.00

[Trebor76]

Hi there,

It's probably not the most efficient way, but have a look at the attached where I've used Microsoft's How to Use a VBA Macro to Sum Only Visible Cells in conjunction with the Unique records only selection from the Advanced Filter option.

I've also noticed that the SPA0024 has two different amounts, which using this methodology may cause confusion as only the first amount will be included in the total.

HTH

Robert

[exceleratedgeek]

Robert,

Thanks for the response but I see that I left something off. I have a data field that i have to sort which will make duplicate SPA's unique. The following is a better picture of the complete data. I will have a sumif for all DO data equaling 1 is my first condition. The next condition is to capture the ship # once and the sum the total amounts. Basically, I could manually delete the duplicate ship SPA0001, SPA0002 within in the DO 1 field and come up with $1,171,900 but I have thousands of lines to go through.

Someone please help

if DO=1,sumif COUNTIF(A1:A9,A1:A9&"") amount
DO SHIP AMOUNT
1 SPA0001 $629,800.00
1 SPA0001 $629,800.00
1 SPA0002 $542,100.00
1 SPA0002 $542,100.00
2 SPA0002 $3,149,000.00
2 SPA0004 $629,800.00
2 SPA0003 $2,519,200.00
2 SPA0005 $1,259,600.00
2 SPA0006 $629,800.00
2 SPA0007 $1,259,600.00
2 SPA0008 $2,168,400.00
2 SPA0009 $542,100.00
2 SPA0022 $629,800.00
2 SPA0022 $542,100.00
2 SPA0023 $1,259,600.00
2 SPA0024 $629,800.00
2 SPA0025 $629,800.00
2 SPA0024 $542,100.00