Any Excel gurus here?

**DaveB** · 6 April 2009, 10:59

Originally posted by realityhack View Post

Would really appreciate your help!

I have a worksheet with about 1000 rows of data, and need to total up the numbers in a column.
Sample:
IDnumber Name1 InstallID1 Date Count Amount1
IDnumber Name2 InstallID2 Date Count Amount2
IDnumber Name2 InstallID3 Date Count Amount2
IDnumber Name3 InstallID4 Date Count Amount3
IDnumber Name4 InstallID5 Date Count Amount4
IDnumber Name4 InstallID6 Date Count Amount4
IDnumber Name4 InstallID7 Date Count Amount4
IDnumber Name5 InstallID8 Date Count Amount5
etc...

Now what I'm after is the sum of the Amount column, excluding duplicates where the Name and amount is the same (but other column info may be unique) ie - sum up the Amount column for the rows in bold, above.

Hope I explained adequately. Is there an easy way to do this?

RH

If you filter on the sum and amount columns for unique records only and use the subtotal function you should get what you need.

=subtotal(2,F:F) Assuming F is the amount column. The 2 tells subtotal to only work with visble cells in the range.

**Badger** · 6 April 2009, 11:11

Originally posted by DaveB View Post

If you filter on the sum and amount columns for unique records only and use the subtotal function you should get what you need.

=subtotal(2,F:F) Assuming F is the amount column. The 2 tells subtotal to only work with visble cells in the range.

No,the 2 means the calculation will be a Count. Use 9 for a Sum.

Alternatively look at using the built in Subtotal functionality in Data > Subtotals

**DimPrawn** · 6 April 2009, 11:21

This is why I like data in databases and not spreadsheets.

**realityhack** · 6 April 2009, 12:03

Thanks chaps - got it in the end.

Selected Name column and: Data>Filter>Advanced Filter>Filter the list, in place, Unique records only - then summed the Amount column.

Any Excel gurus here?