Can you Sum between two dates

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

Robert Gillard

I have a worksheet that is automatically updated with a fresh row of data
every day. (say column A is the daily date and column B is the value that I
need totaled for each month)

On a seperate sheet I want to have a row that will summarise each months
work from sheet1. So in simple English I need to say check column A on
sheet1 and sum all of the values in column B between the 1st of Jan to the
31st Jan, then on the next row I will say check column A on sheet1 and sum
all of the values in column B between the 1st of Feb to the 28th Feb - and
as Feb is not yet over this will be a cumulative total until we get to the
1st of March, at which time it automatically becomes the grand total for the
month. And so on through the year.

Could any body give me some thoughts on the best way to do this. (although I
have only detailed one column there is infact 24 columns to total each
month.)
 
This sums values between certain dates

Assuming:
D1: 1-Jan-2005
E1: 1-Feb-2005
A1:A100: Dates
B1:B100: Values

=SUMPRODUCT(($A$1:$A$100>=$D$1) * ($A$1:$A$100<$E$1), $B$1:$B$100)
 
I have a worksheet that is automatically updated with a fresh row of data
every day. (say column A is the daily date and column B is the value that I
need totaled for each month)

On a seperate sheet I want to have a row that will summarise each months
work from sheet1. So in simple English I need to say check column A on
sheet1 and sum all of the values in column B between the 1st of Jan to the
31st Jan, then on the next row I will say check column A on sheet1 and sum
all of the values in column B between the 1st of Feb to the 28th Feb - and
as Feb is not yet over this will be a cumulative total until we get to the
1st of March, at which time it automatically becomes the grand total for the
month. And so on through the year.

Could any body give me some thoughts on the best way to do this. (although I
have only detailed one column there is infact 24 columns to total each
month.)
Click to expand...

One way is to use the SUMIF function:

=SUMIF(DataSheet!A:A,">="&A1,DataSheet!B:B) -
SUMIF(DataSheet!A:A,">="&B1,DataSheet!B:B)

Where A1 contains 1 Jan 2005 and B1 contains 1 Feb 2005

It should be easy to set up an appropriate matrix, so you can enter this
formula once, and copy/drag it down and across to do subtotal all the columns
and months appropriately.


--ron
 
Back
Top