sum if duplicate

[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.

 

[Don Guillett]

something like this??
=IF(COUNTIF(A2:A22,2002)>1,SUMIF(A2:A22,2002,B2:B22),"")

 

[Peo Sjoblom]

One possible way

=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>=2)*(B2:B10))

 

[Aladin Akyurek]

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.