I am doing a worksheet that has sales people, their district and % of
district total for each person. There are many districts on this report. I
don't want to manually do it for each district. How can I get a formula that
will only use the total of each district for what district that person is in?

Sample data

District Name Loan Vol % District Vol
Central Person 1 49,538 Should be 17% (49,538/287,605 (Central's Total)
Central Person 2 9,680
Central Person 3 13,230
Central Person 4 14,180
Central Person 5 115,155
Central Person 6 8,592
Central Person 7 77,231
North Person 8 83,493
North Person 9 131,365
North Person 10 16,042
North Person 11 7,805
North Person 12 30,000
North Person 13 135,122
North Person 14 12,846
North Person 15 4,476
North Person 16 17,043
North Person 17 3,306
South Person 18 9,013
South Person 19 15,725
South Person 20 5,982
South Person 21 30,840

If you can sort the data by district or salesperson and then use
datasubtotal it will insert subtotals at each change in district or
salesperson.

If you don't want to sort the list for some reason, another good solution
would be to use a pivot table.

Carlos

"Jody" wrote in message
...
I am doing a worksheet that has sales people, their district and % of
district total for each person. There are many districts on this report. I
don't want to manually do it for each district. How can I get a formula
that
will only use the total of each district for what district that person is
in?

Sample data

District Name Loan Vol % District Vol
Central Person 1 49,538 Should be 17% (49,538/287,605 (Central's Total)
Central Person 2 9,680
Central Person 3 13,230
Central Person 4 14,180
Central Person 5 115,155
Central Person 6 8,592
Central Person 7 77,231
North Person 8 83,493
North Person 9 131,365
North Person 10 16,042
North Person 11 7,805
North Person 12 30,000
North Person 13 135,122
North Person 14 12,846
North Person 15 4,476
North Person 16 17,043
North Person 17 3,306
South Person 18 9,013
South Person 19 15,725
South Person 20 5,982
South Person 21 30,840

Pivot tables were made for this

http://peltiertech.com/Excel/Pivots/pivotstart.htm


otherwise use

=SUMPRODUCT(--(A2:A500="North"),--(B2:B500="Person10"),C2:C500)


Regards,

Peo Sjoblom



"Jody" wrote:

I am doing a worksheet that has sales people, their district and % of
district total for each person. There are many districts on this report. I
don't want to manually do it for each district. How can I get a formula that
will only use the total of each district for what district that person is in?

Sample data

District Name Loan Vol % District Vol
Central Person 1 49,538 Should be 17% (49,538/287,605 (Central's Total)
Central Person 2 9,680
Central Person 3 13,230
Central Person 4 14,180
Central Person 5 115,155
Central Person 6 8,592
Central Person 7 77,231
North Person 8 83,493
North Person 9 131,365
North Person 10 16,042
North Person 11 7,805
North Person 12 30,000
North Person 13 135,122
North Person 14 12,846
North Person 15 4,476
North Person 16 17,043
North Person 17 3,306
South Person 18 9,013
South Person 19 15,725
South Person 20 5,982
South Person 21 30,840

Hi,

How about using this formula in the row 2 in the column where you want to
put the percentages.

Assume that Column A has the district and Column D has the total volume.

=A2/sumif($A$2:$A$20,A2,$D$2:$D$20)

Then copy the formula to the other rows.

Alok
"Jody" wrote in message
...
I am doing a worksheet that has sales people, their district and % of
district total for each person. There are many districts on this report. I
don't want to manually do it for each district. How can I get a formula
that
will only use the total of each district for what district that person is
in?

Sample data

District Name Loan Vol % District Vol
Central Person 1 49,538 Should be 17% (49,538/287,605 (Central's Total)
Central Person 2 9,680
Central Person 3 13,230
Central Person 4 14,180
Central Person 5 115,155
Central Person 6 8,592
Central Person 7 77,231
North Person 8 83,493
North Person 9 131,365
North Person 10 16,042
North Person 11 7,805
North Person 12 30,000
North Person 13 135,122
North Person 14 12,846
North Person 15 4,476
North Person 16 17,043
North Person 17 3,306
South Person 18 9,013
South Person 19 15,725
South Person 20 5,982
South Person 21 30,840