Macro Help

aubeidulla

New Member
Joined
Jan 19, 2005
Messages
16
I am working on a fairly large spreadsheet. I have a list of names and their production (sales) over a twelve month period. I want to be able to total the production of each individual (since their names show up multiple times). I dont' want to use the subtotal function because it takes excel a long time to subtotal the entire spreadsheet. Also after subtotaling i will need to sort the information as well and with subtotal i cannot sort without losing the subtotals. Is there a way for me to do this using a macro? Thanks.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Why would SumProduct not work?
Book1
ABCDEFGHI
1John10JohnSteveJoePamHenry
2Steve121301741967084
3Joe8
4Pam5
5Henry20
6John55
7Steve75
8Joe90
9Pam30
10Henry22
11John10
12Steve12
13Joe8
14Pam5
15Henry20
16John55
17Steve75
18Joe90
19Pam30
20Henry22
Sheet1

HTH
 

aubeidulla

New Member
Joined
Jan 19, 2005
Messages
16
Here is my current format:

Agent Name LF Deal TTC Office NP Agent NP Brach Metro
Farber , Jean $10,017 $3,719 $5,696 1358 NY
Tynan , Diane $255 $83 $157 1358 NY
Alves , Rui $10,625 $4,994 $4,994 1360 CT
SEIDEN , MARK $12,500 $1,763 $9,988 1250 CT
Hughes , Margaret $875 $263 $559 1460 CT
Hughes , Margaret $775 $233 $495 1460 CT
Hughes , Margaret $700 $211 $447 1460 CT
Kennedy , Nancy $12,855 $3,081 $9,002 2502 CA
Kennedy , Nancy $0 ($452) $452 2502 CA
MacIntyre , Helena $7,375 $2,738 $4,194 3212 NY
McDuffey , Deborah $1,209 $526 $610 3212 CT
Wiland-Fillebrown $12,855 $3,127 $5,936 3500 IL
Wiland-Fillebrown $0 ($298) $298 3500 IL
FioRito , Rebekah $600 $169 $395 3612 FL
FLOOD , PATRICIA $700 $303 $355 3700 NY
Kotlyar , Tatiana $700 $329 $329 2600 FL
Lopez , Laura $750 $353 $353 2600 CA
Luburic , Vesna $800 $263 $489 1500 IL
McGinnis , James $7,925 $2,794 $4,656 1500 OH
McGinnis , James $8,568 $3,020 $5,034 1500 OH
McGinnis , James $8,041 $2,834 $4,724 1500 OH
McGinnis , James $12,391 $4,368 $7,280 1500 OH
Westervelt , Janet $28,125 $13,219 $12,969 1700 FL
Berliner , Lisa $525 $207 $286 1700 FL
Curro , Alma $14,625 $6,874 $6,874 1700 FL
Keane , Kay $238 $55 $169 1750 CA
Keane , Kay $25,000 $4,318 $13,307 1750 CA
Kevelin , Christina $6,641 $2,466 $3,777 1800 OH
Kevelin , David $6,641 $2,466 $3,777 1800 OH
Nowak , Carine $26,250 $7,403 $17,273 2000 CA
Ornstein , David $1,903 $537 $1,252 2025 CA
Russell , Donald $10,824 $3,052 $7,122 2025 CA
Russell , Donald $5,267 $1,485 $3,466 2025 CA

What i would like to do is the following:
Sum the production for each agent so their names are not repeating. After summing I would lke be able to sort by Branch, Agent, or even Metro without losing the sums.

How would you write the sumproduct formula for the above? I am not familiar with Pivot Tables (Please Advise).

Thanks.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020

ADVERTISEMENT

I would definitely look into Pivot Tables if I were you. Excel's own help does a much better job of explaining the basics than I could hope to try to do. If you work through Excel's help and experiment with PT's and get stuck, just post back.
 

aubeidulla

New Member
Joined
Jan 19, 2005
Messages
16

ADVERTISEMENT

I tried the pivot table approach. It did'nt work. I currently have 12,200 rows of information and I believe this was too much info for the Pivot Tables to handle. Any other suggestions. Unless I did it wrong.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
I tried the pivot table approach. It did'nt work. I currently have 12,200 rows of information and I believe this was too much info for the Pivot Tables to handle

How did it not work?

Is Excel throwing up errors or are you not getting the expected result?

I know a pivot table can handle that many rows.
 

Forum statistics

Threads
1,147,690
Messages
5,742,638
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top