Summing a column where matches adjacent column and colating results

**rlamb** · 01-16-2013, 06:39 PM

I have a question which on the face of it seems very simple but have not found a solution whilst searching this and many other forums.
I am using Excel 2010 and have a list of customer numbers with associated revenue. In this list the same customer number will appear repeatedly for each sale. In reality the list is over 2000 lines long with a possibility of 300 different customer.
CUS # SALE $
11111 10
22222 22
55222 32
11111 20
62626 43
11111 11
22222 30

What I want is an output than sums the total per customer number, but cannot reference the customer number in any formulae as I will not know it in advance.
The output should look like this

CUS # SALE $

11111 41
22222 52
55222 32
62626 43

I have tried with a formulae that says something like =SUMIF(A1:A2000,"criteria",B1:B2000) where criteria = 11111, however what I want is a similar formulae that dynamically subtotals all the sales per customer without me having to know in advance the customer number. I want the formulae to learn the criteria from the given data.

Any help is appreciated.

**Moo the Dog** · 01-16-2013, 07:35 PM

See my attached sheet and let me know if it is what you are trying to do. The formulas used are:

To get a unique+sorted list (in cell D2 and filled down):
=IFERROR(SMALL($A:$A,1+SUMPRODUCT(COUNTIF($A:$A,D$1:D1))),"")

To get the sum per customer from that list (in cell E2 and filled down):
=IF(D2="","",SUMIF($A:$A,D2,$B:$B))

- Moo