Filter and subtotal

Hi,

I am using Excel 2007 and I would appreciate if you can suggest me how to obtain total shares quantity for each company and for each year. Data looks like this.

YEAR Company Shares quantity
1992 A1 10
1992 A2 22
1992 A3 222
1992 A4 33
1992 A5 112
1992 B1 32
1992 B2 232
1992 B3 3232
1992 B4 22
1992 B5 11
1992 B6 212
1992 C1 1121
1992 C2 121
1992 C3 111
1992 C4 1212
1992 C5 33
1993 A1 111
1993 A2 212
1993 A3 980
1993 A4 34
1993 A5 33
1993 B1 44
1993 B2 333
1993 B3 3443
1993 B4 233
1993 B5 343
1993 C1 3243
1993 C2 2343
1993 C3 222
1993 C4 111
1993 C5 232

Clarification: A1,A2,A3,A4 and A5 are the shareholders of company A. I need total shares quantity of A1, A2, A3, A4 and A5 (i.e. for company A) for year 1992, 1993...and so on. Notice that there may be more or less than 5 shareholders in each year for company A. Accordingly company B has six shareholders B1.B2,B3,B4,B5 and B6 for year 1992 but only five shareholders for year 1993

Assumption: There are 15 years of data and 1000 companies (not shareholders).
Please help me in this problem.

Thanks.

Hi, maybe

=SUMPRODUCT((LEFT(B2:B100,1)="A")*(A2:A100=1992)*C2:C100)

Probably you' ll have to adjust the range or conditions.

Regards

Originally Posted by CANAPONE

Hi, maybe

=SUMPRODUCT((LEFT(B2:B100,1)="A")*(A2:A100=1992)*C2:C100)

Probably you' ll have to adjust the range or conditions.

Regards

Thanks for the response. However, I am not able to get the result with the formula. To simplify, assume that there is already a column with A1, A2,...A6 replaced by A. So it is implicit that number of A is equal to the number of shareholders. Data looks like this :


YEAR Company Shares quantity
1992 A 10
1992 A 22
1992 A 222
1992 A 33
1992 A 112
1992 B 32
1992 B 232
1992 B 3232
1992 B 22
1992 B 11
1992 B 212
1992 C 1121
1992 C 121
1992 C 111
1992 C 1212
1992 C 33
1993 A 111
1993 A 212
1993 A 980
1993 A 34
1993 A 33
1993 B 44
1993 B 333
1993 B 3443
1993 B 233
1993 B 343
1993 C 3243
1993 C 2343
1993 C 222
1993 C 111
1993 C 232

The problem is to find the subtotal of each company for each year i.e A for 1992, B for 1992, C for 1992, A for 1993 and so on...

I hope that this will help you to arrive at a solution. Also I would like to notify you that there are more than 1000 companies and I don't think it is feasible to change the formula each time for each companies.

Regds

Originally Posted by DonkeyOte

This would appear to be crying out for a Pivot Table....

Could you help me in this situation?