sum if duplicate

  • Thread starter Thread starter shelley
  • Start date Start date
S

shelley

Column A contains account numbers, some are duplicates
(they are in numeric order). Column H contains currency
amounts. How can I sum the currency amounts for only the
account numbers which have more than one occurance? I
would like this to go on a separate page.

Acct No. Amount
2001 42.28
2002 143.12
2002 61.00
2003 4.33
2004 1.20
2004 .89

Thanks for the help.
 
I assume you want to sum *per* duplicate number. This assumption requires
that you extract non-distinct Acct. No's to the destionation sheet. Once you
accomplish that, you can then use a simple SumIf formula.

Supposing that you set up a criteria range in A1:A2 on the destination
sheet, where A1 is left empty and A2 houses the following formula:

=ISNUMBER(MATCH(Sheet1!A2,Sheet1!A3:INDEX(Sheet1!A:A,MATCH(9.99999999999999E
+307,Sheet1!A:A))))

you should be able to get the following in A3:A5, given the sample you
provided on Sheet1:

{"Acct#";2002;2004}

The SumIf formula that you need in B4 would be:

=SUMIF(Sheet1!A:A,A4,Sheet1!H:H)

which you copy down as far as needed.
 
Back
Top