Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Data from Date Range base on relative dates
I have a though one I've been trying to figure out, any help would be most
appreciated. Spreadsheet is set up like this.. Columns A B C D E F 1 01/01 01/02 01/03 01/04 01/05 2 01/01 100 101 102 103 104 3 01/02 100 102 103 104 105 4 01/03 200 201 202 203 204 5 01/04 300 301 302 303 304 In a cell I want it to calculate the sum for a date range (using relative dates) but only if a column equals today's date So for instance, in the first cell I'm looking to do this: if today was 01/03 it would first look for "today()" (in row 1), then it would calculate the sum of the first day of the month (from column A), through yesterday (from column A) giving me a value of 102+103=205. Then in another cell if today was 01/03 (in row 1) it would calculate the sum of today (from column A) through the end of the month (from column A, assuming the spreadsheet continued to 01/31) But using the example above it would be 203+303=506. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Data from Date Range base on relative dates
Assuming source table in Sheet1,
with B1:AF1 containing dates 1-31 listed across and A2:A32 containing dates 1-31 listed down In say, Sheet2, Assume the reference date is entered in A1 down In B1: =SUM(OFFSET(Sheet1!$A$2,,MATCH(A1,Sheet1!$B$1:$AF$ 1,0),MATCH(A1,Sheet1!$A$2:$A$32,0)-1)) will return the required sum from "top", viz. your: the sum of the first day of the month .. through yesterday In C1: =SUM(OFFSET(Sheet1!$A$32,,MATCH(A1,Sheet1!$B$1:$AF $1,0),-(31-MATCH(A1,Sheet1!$A$2:$A$32,0)))) returns the required sum from "bottom", viz. your sum of today .. through the end of the month -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "GaryS" wrote: I have a though one I've been trying to figure out, any help would be most appreciated. Spreadsheet is set up like this.. Columns A B C D E F 1 01/01 01/02 01/03 01/04 01/05 2 01/01 100 101 102 103 104 3 01/02 100 102 103 104 105 4 01/03 200 201 202 203 204 5 01/04 300 301 302 303 304 In a cell I want it to calculate the sum for a date range (using relative dates) but only if a column equals today's date So for instance, in the first cell I'm looking to do this: if today was 01/03 it would first look for "today()" (in row 1), then it would calculate the sum of the first day of the month (from column A), through yesterday (from column A) giving me a value of 102+103=205. Then in another cell if today was 01/03 (in row 1) it would calculate the sum of today (from column A) through the end of the month (from column A, assuming the spreadsheet continued to 01/31) But using the example above it would be 203+303=506. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a range of numbers associated with various dates | Excel Discussion (Misc queries) | |||
return a date from range, date is between dates in two other cells | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Adding dates to get a date | Excel Worksheet Functions | |||
Address Base - Relative | Excel Discussion (Misc queries) |