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

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

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