Advanced filtering and sums

[blkane]

I have a large table with columns of data that I wish to consolidate similar
lines of data using advanced filters. However, the last column is $$
amounts. When I initiate the filters to show unique records, the columns
preceding the $$ filter correctly. However, in the amount column, only the
first cell of $$ is captured. Is there a way to filter on unique records and
have it sum the amount column?

 

[Pete_UK]

Use the Advanced Filter to produce unique records somewhere else (eg
on a separate sheet). Then you can use SUMIF to produce sums for each
unique record.

Hope this helps.

Pete

 

[Peo Sjoblom]

Use something like this, select both the range you want the unique records
from and the dollar amount range

Assume the first data cell (not header) in the range where you want the
unique records from is A4

In row number two in an empty cell (let's say H2) put

=COUNTIF($A$4:A4,A4)=1


now apply the filter, select copy to another location (at least I find it
easier to do it this way)
assume the list range is $A$3:$B$200, then the criteria range should be
$H$1:$H$2

apply the filter and you should have your unique records plus the dollar
amounts and you can just sum them accordingly


--


Regards,


Peo Sjoblom



--


Regards,


Peo Sjoblom

 

[blkane]

It did but it wasn't the final solution. The formula I needed was
sumproduct. You gave me a clue to google on. I was able to reduce my line
items by 50%. Thanks