Summary of values from a date range
 

I have a sheet where I enter invoices raised. Can I automatically get a
formula to look at the extent of a range of dates and provide a monthly
total.

The problem is that there is going to be a different amount of entries
per month so the formula must be able to select all Jan 05, Feb 05, Mar
05 values, etc from a list.

Hi
This sounds like a job for Data/Pivot Table, which is a fantastic tool for
summarising data. Here's an intro:
http://www.cpearson.com/excel/pivots.htm

--
Andy.


"Redsmartie" wrote in
message ...
I have a sheet where I enter invoices raised. Can I automatically get a
formula to look at the extent of a range of dates and provide a monthly
total.

The problem is that there is going to be a different amount of entries per
month so the formula must be able to select all Jan 05, Feb 05, Mar 05
values, etc from a list.

Hi

you can use the SUMPRODUCT function for this, e.g.
with your dates in column A and the amounts in column C
(this will give you the value of invoices for Jan 2005
=SUMPRODUCT(--(MONTH(A2:A500)=1),--(YEAR(A2:A500)=2005),C2:C500)

or you can use a pivot table,

click in your list of invoices
choose data / pivot table and pivot chart report
choose next
check the range and choose next
choose new worksheet and finish

now drag the dates to the where it says row
drag the values to where it says data
right mouse click on the dates and choose group and show detail
choose group
select both month & year
click OK

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Redsmartie" wrote in
message ...
I have a sheet where I enter invoices raised. Can I automatically get a
formula to look at the extent of a range of dates and provide a monthly
total.

The problem is that there is going to be a different amount of entries per
month so the formula must be able to select all Jan 05, Feb 05, Mar 05
values, etc from a list.

Thanks,

I'll give that a try

On 2005-04-01 17:24:56 +0100, "JulieD" said:

Hi

you can use the SUMPRODUCT function for this, e.g.
with your dates in column A and the amounts in column C
(this will give you the value of invoices for Jan 2005
=SUMPRODUCT(--(MONTH(A2:A500)=1),--(YEAR(A2:A500)=2005),C2:C500)

or you can use a pivot table,

click in your list of invoices
choose data / pivot table and pivot chart report
choose next
check the range and choose next
choose new worksheet and finish

now drag the dates to the where it says row
drag the values to where it says data
right mouse click on the dates and choose group and show detail
choose group
select both month & year
click OK

let us know how you go

"Redsmartie" wrote in
message ...
Thanks,

I'll give that a try

On 2005-04-01 17:24:56 +0100, "JulieD"
said:

Hi

you can use the SUMPRODUCT function for this, e.g.
with your dates in column A and the amounts in column C
(this will give you the value of invoices for Jan 2005
=SUMPRODUCT(--(MONTH(A2:A500)=1),--(YEAR(A2:A500)=2005),C2:C500)

or you can use a pivot table,

click in your list of invoices
choose data / pivot table and pivot chart report
choose next
check the range and choose next
choose new worksheet and finish

now drag the dates to the where it says row
drag the values to where it says data
right mouse click on the dates and choose group and show detail
choose group
select both month & year
click OK