Solved: unique values with sumif

[philfer]

I have a spreadsheet with account numbers and balances and am doing a sumif on them. However some clients have subaccounts and thus doing a sumif on the account number (which I want) gives several of the same values. Is there a way in VBA to fill a column with sumif formulae but only return unique results?

[Bob Phillips]

SUMIF will return a result of an amount range where another range equals the criteria. It doesn't produce a list of the items that match. Are you after the latter, or just a count of each of the sub-accounts within the account? Or even something else.

[philfer]

Lets say I have :-


Acc Sub Amount
Code Code

57187 25 1,000
57187 10 2,000
57187 30 1,500
58186 25 8,000
58186 45 9,000
52189 25 8,000

In the end column I enter =SumIF("A:A",A1,"C:C") then copy the formula down.

In the first three cells I get 4,500 then in the next two I get 17,000 then in the last cell I get 8,000

Obviously my sheet is much larger and with more "stuff" in it but this is just an example.

I just want the total for 57187 once i.e. 4,500 to appear once, then 17,000 the total for 58186 to appear once and so on

[Bob Phillips]

=IF(COUNTIF($A$1:A1,A1)>1,"",SUMIF(A:A,A1,C:C))