Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can you Sum between two dates
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.) |
#2
|
|||
|
|||
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) -- Rob van Gelder - http://www.vangelder.co.nz/excel "Robert Gillard" wrote in message . uk... 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.) |
#3
|
|||
|
|||
On Fri, 25 Feb 2005 21:30:32 GMT, "Robert Gillard"
wrote: 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.) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Excell to sort dates that range from 1800 to 1900's | New Users to Excel | |||
How sort dates just by day/month and NOT year if all 3 given in ce | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) |