totaling criteria

[Joanne]

Using WinXP Pro and MSOffice 2003 Pro
Col D has customer name
Col E has customer location
Col G has customer amounts
There can be many locations to a customer name, and many line items to
the combination CustName and CustLocation

I need to read Col D, then first location of Col E which may have many
line items, then I need to do a sum of the numbers in col G that belong
to col D and col E combined.

I want to repeat this thru a 3000+ spread sheet, breaking out a total
for each Customer Name with each of it's locations.

Example
Col D Col E Col G
ABC Co Chicago 22.22
ABC Co Chicago 8.00
ABC Co Chicago 15.00 end of location-get total for G
ABC Co New York City 2.22
ABC Co New York City 18.00
ABC Co New York City 215.00 end of location-get total for G
ABC Co Miami 22.22
ABC Co Miami 8.00
ABC Co Miami 15.00
ABC Co Miami 22.22
ABC Co Miami 8.00
ABC Co Miami 15.00
Bats and Balls Springfield 55.55 end of name/location-get total for G
Bats and Balls Los Angeles 44.44 end of name/location-get total for G

etc etc, where the code recognizes ABC Co in chicago, totals the Chicago
numbers, then recognizes ABC Co in NYC, totals, gets ABC Co Miami,
totals, Recognizes Bats and Balls, Springfield, totals, Bats and Balls
Los Angeles, totals, etc etc.

Do I need to code this or will filters in Excel do this job. Being a
newbie here, I would appreciate any help you can give, as always.
Joanne

 

[Dave Peterson]

This sounds like a perfect project to learn about pivottables.

But if you don't want to, you could add a helper column with a formula that
combines column D and E:

With headers in row 1:
=d2&"---"&e2
and drag down

Then sort by this column (if needed)

And use data|subtotals to find the sum of column G for each cust/location
combination.

Then you can use the outlining symbols to the left to hide/show details. Try
clicking on that 1 and 2 at the top of those outlining symbols, too.

But back to pivottables...

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx