Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 185
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"