Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
totaling criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
totaling criteria
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/assistan...lconPT101.aspx Joanne wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
totaling $$ | Excel Discussion (Misc queries) | |||
Sub-Totaling | Excel Discussion (Misc queries) | |||
Sub-Totaling | Excel Worksheet Functions | |||
Totaling criteria from 2 columns | Excel Worksheet Functions | |||
Help with totaling a columns in VBA | Excel Programming |