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" <[email protected]> wrote in
message news:[email protected]...
>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" <[email protected]> wrote in
message news:[email protected]...
>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" <[email protected]> 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" <[email protected]> wrote in
message news:[email protected]...
> Thanks,
>
> I'll give that a try
>
> On 2005-04-01 17:24:56 +0100, "JulieD" <[email protected]>
> 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
>