Adding Data from Date Range base on relative dates

[GaryS]

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.

 

[Max]

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

---