Aggregating Cases

**Toby2010** · 01-26-2011, 07:45 PM

Hi there,

In Column 1 are firm names, each firm appears several times:

Adidas
Adidas
Puma
Puma
Puma
BP

In Column 2 are number of employees for each firm:
Adidas 20
Adidas 133
Puma 2
Puma 777
Puma 0
BP 10

I want to have a list where each firm appears only one time and the values in column two are aggregated:
Adidas 154
Puma 779
BP 10

How shall I do this?

Thanks
Toby

**JBeaucaire** · 01-26-2011, 08:06 PM

1) Highlight the column A values
2) Use the Data > Filter > Advanced Filter > Copy to another location (select target column) > [x] Unique values only to get the short list in another column

3) Assuming you put that list in column D, the first company name is in D2, put this formula in E2, then copy down:

=SUMIF(A:A:, D2, B:B)

**Toby2010** · 01-26-2011, 08:35 PM

What do I enter for:
List Range: the values in column A (firms)?
Criteria Range:
Copy to:

?
Thanks a lot
Toby

**JBeaucaire** · 01-26-2011, 08:39 PM

1) Highlight column A (this will make column A the list range)

2) Copy To: (Suggested column D, so D1, but click on any empty column)

**Toby2010** · 01-26-2011, 09:24 PM

The values are not correct:
- A colum is the company name (ticker)
- B colum is number of people
- D column is ticker with unique values
- E column is the sum - but for Ticker A for example it should be 20 and not 24

TICKER DIRMULTIPLE Ticker
A 3 A 24
A 3 AA 25
A 1 ABCW 10
A 2 ACAT 12
A 1 ACF 22
A 1 ACTL 14
A 1 ADBE 16
A 2 AEO 25
A 1 THG 24
A 2 AFL
A 3 AGL
AA 0 AGN
AA 0 AHG
AA 1 AIG
AA 2 AKS
AA 3 ALLP.OB
AA 0 ALOG
AA 2 AM
AA 0 AMAT
AA 1 AME
AA 1 AMFI
AA 3 ANN
AACC 4 AOS
AACC 2 APD
AACC 0 APOG

**JBeaucaire** · 01-26-2011, 11:25 PM

The SUMIF() formula is the correct one. That's not at issue.

The issue is you have errors in your data, errors we will never spot with you posting sample text excerpts.

Post up your workbook with the SUMIF() formula already in it so we can see your data and how you put in the formula. This way we can see what's wrong.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**Toby2010** · 01-27-2011, 06:56 AM

Ok, there were some rows hidden - I could solve that now.

Another problem: colum D does not show all firms - it stops around letter D... please have a look at the file attached.

Thanks a lot
T

**JBeaucaire** · 01-27-2011, 10:21 AM

Excel is funny sometimes. You selected the entire column A, then activated the ADV filter. Did you notice that the LIST RANGE was actually stopping around row 12000? Just make sure the list range goes down far enough to encompass all the data, then try it again. I set it to A1:A20000 and all the values appeared.