Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David

I really need some help on this please! Have I explained what I want clearly
enough?
cheers
--
David


"Prospect" wrote:

Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David

David,

Have a field with the actual date (can be month/year, but it needs to be a date, not two columns one
with year and month) then use the date as a row field, and use the grouping option, choosing
quarters.

HTH,
Bernie
MS Excel MVP


"Prospect" wrote in message
...
Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David

sorry Bernie, not sure that I've understood you correctly.
Assuming I can change my month and year columns into a date (any suggestions
- can I use the concatenate function for this?), how do I change this to a
row field? Currently my rows are the expenditure items, e.g.
Year Month Category Amount
2007 Feb Equipment £100
2007 Feb Training £200

Also, how do I get them grouped by category in each quarter?

Thanks for you help, feeling a bit of a newbie!

cheers
David
--
David


"Bernie Deitrick" wrote:

David,

Have a field with the actual date (can be month/year, but it needs to be a date, not two columns one
with year and month) then use the date as a row field, and use the grouping option, choosing
quarters.

HTH,
Bernie
MS Excel MVP


"Prospect" wrote in message
...
Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David

David,

I'm sorry that I wasn't clear: I had just answered a Pivot Table question, and sort of went into
your question with that mind set.

Let's say that your table is in columns A through D, with headers in row 1. In cell E1, enter
"Date". Then in cell E2, enter the formula

=DATEVALUE(B2 & " 1, " & A2)

and format that cell for any date option that you prefer, and copy that down column E to match your
data.

Then select the entire data table (column A through E, including all rows) and select Data / Pivot
Table... (the "Pivot Table" option wording depends on your version, but you get the idea) and
click "Finish".

That should create a new sheet with a pivot table template, and the Pivot Table Field List dropdown
showing. Drag the button from the dropdown with the "date" label to the row field area, and drag
the "category" button to the row field area, then drag your 'value' button to the data field area.
Right click the 'value' button and select sum. This will create a pivot table with the values
summed by category and date. Then right click the grey date button on the pivot table, choose
"Group" and select "Quarters" in the 'by' window.

For more on grouping in Pivot Tables, visit:

http://www.contextures.com/xlPivot07.html

HTH,
Bernie
MS Excel MVP


"Prospect" wrote in message
...
sorry Bernie, not sure that I've understood you correctly.
Assuming I can change my month and year columns into a date (any suggestions
- can I use the concatenate function for this?), how do I change this to a
row field? Currently my rows are the expenditure items, e.g.
Year Month Category Amount
2007 Feb Equipment £100
2007 Feb Training £200

Also, how do I get them grouped by category in each quarter?

Thanks for you help, feeling a bit of a newbie!

cheers
David
--
David


"Bernie Deitrick" wrote:

David,

Have a field with the actual date (can be month/year, but it needs to be a date, not two columns
one
with year and month) then use the date as a row field, and use the grouping option, choosing
quarters.

HTH,
Bernie
MS Excel MVP


"Prospect" wrote in message
...
Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David

Thanks very much, Bernie, that sounds reasonably clear - I'll give it go!
--
David


"Bernie Deitrick" wrote:

David,

I'm sorry that I wasn't clear: I had just answered a Pivot Table question, and sort of went into
your question with that mind set.

Let's say that your table is in columns A through D, with headers in row 1. In cell E1, enter
"Date". Then in cell E2, enter the formula

=DATEVALUE(B2 & " 1, " & A2)

and format that cell for any date option that you prefer, and copy that down column E to match your
data.

Then select the entire data table (column A through E, including all rows) and select Data / Pivot
Table... (the "Pivot Table" option wording depends on your version, but you get the idea) and
click "Finish".

That should create a new sheet with a pivot table template, and the Pivot Table Field List dropdown
showing. Drag the button from the dropdown with the "date" label to the row field area, and drag
the "category" button to the row field area, then drag your 'value' button to the data field area.
Right click the 'value' button and select sum. This will create a pivot table with the values
summed by category and date. Then right click the grey date button on the pivot table, choose
"Group" and select "Quarters" in the 'by' window.

For more on grouping in Pivot Tables, visit:

http://www.contextures.com/xlPivot07.html

HTH,
Bernie
MS Excel MVP

Try this:

=SUMPRODUCT(--($A$1:$A$100=2007),--($B$1:$B$100=1),--($B$11:$B$100<=3),$C$1:$C$100)

assumed the year information is in A1:A100 and the month information is in
B1:B100 and the amounts are in C1:C100

this is for the year 2007 but you can change to what ever year you want

this is for first quarter for other quarters you have to change =1 and <=3
to:
second quarter: =4 and <=6
third quarter: =7 and <=9
last quarter: =10 and <=12

Thanks,
--
Farhad Hodjat


"Prospect" wrote:

Hi
I have a list of expenditures and want to create a report that wil total
them by category in each quarter.

So far my list has headings of:
Year Month Category Amount

I would appreciate suggestions on how to set this up please!
--
David