Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a range of numbers associated with various dates missin44 Excel Discussion (Misc queries) 3 January 18th 07 04:31 PM
return a date from range, date is between dates in two other cells NN Excel Discussion (Misc queries) 1 September 28th 06 10:05 PM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM
Adding dates to get a date [email protected] Excel Worksheet Functions 4 August 15th 05 08:51 PM
Address Base - Relative CycleFitness Excel Discussion (Misc queries) 0 February 2nd 05 05:11 PM


All times are GMT +1. The time now is 02:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"