Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula to sum
I have a database with the following columns: store number, date (YMD) &
costs. The database covers several years. I need a formula to sum all costs for a any particular store and for each month. example: What is the sum of all costs for store 2143 for the month of Jan in 06? 2143 1/2/06 27.50 2143 1/5/06 84.75 2143 4/5/06 47.50 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula to sum
Here's the short answer: PIVOT TABLE
Here's how... Assuming your posted data is in cells A2:C4 with A1: Store B1: Date C1: Cost Then.... From the Excel Main Menu: <Data<Pivot Table Use: Excel..Click [Next] Select your data..Click [Next] Click the [Layout] button ROW: Drag the Store field here COLUMN: Drag the Date field here DATA: Drag the Cost field here If it doesn't list as Sum of Cost...dbl-click it and set it to Sum Click [OK] Select where you want the Pivot Table.Click [Finish]. Not quite there yet.... Right Click on the Date heading in the Pivot Table Select: Group and show detail...Group Selec only: Months....Click OK Now the Pivot Table displays: Stores in the left column Months in the top row Costs per month in the data area To refresh the Pivot Table, just right click it and select Refresh Data Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Charlie7805" wrote in message ... I have a database with the following columns: store number, date (YMD) & costs. The database covers several years. I need a formula to sum all costs for a any particular store and for each month. example: What is the sum of all costs for store 2143 for the month of Jan in 06? 2143 1/2/06 27.50 2143 1/5/06 84.75 2143 4/5/06 47.50 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula to sum
Charlie
You need a Pivot Table, it will do everything for you and will be a revelation. You might start here http://www.nickhodge.co.uk/gui/datam...ablereport.htm -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "Charlie7805" wrote in message ... I have a database with the following columns: store number, date (YMD) & costs. The database covers several years. I need a formula to sum all costs for a any particular store and for each month. example: What is the sum of all costs for store 2143 for the month of Jan in 06? 2143 1/2/06 27.50 2143 1/5/06 84.75 2143 4/5/06 47.50 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|