If I understand what you're asking for correctly, I think we can do this
another way (not sure if INDEX/MATCH would work anyhow).

If I understand you, you want a formula on row 3 (or somewhere) that will
total the values in columns AN, AT, AZ, BF, BL, BR, BX, CD, CJ, CP, CU and DA
based on the month and day of the month. For row 3 in those columns, the day
of the month must be the 3rd or after, and the month must be less than the
current month (using TODAY()). So on the 5th of April, the formulas should
return results for the 3rd, 4th and 5th of Jan, Feb and March.

We can use a relatively simple formula repeated with minor modifications to
get the answer.

These formulas are assumed to go in the same row (3 through 15) that you are
interested in. Here is the basic format of the formula for the values in row
3 (3rd of the month)
=IF(AND(MONTH(AN$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AN3,0)
to modify that to get the value for Feb, column AT all we do is change the
two instances of AN in it:
=IF(AND(MONTH(AT$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AT3,0)
we can repeat this logic all the way through November, but December is a
special case since there is no month #13.
for December (column DA) the formula would look like this:
=IF(AND(MONTH(DA$1)=MONTH(TODAY()),(DAY(TODAY())= ROW())),DA3,0)

What we do is simply add the results of these tests together in a single
formula (looks long and ugly, but examine it and you'll see it is just the
basic formula repeated 11 times plus the December formula and adding the
results of each together:

=IF(AND(MONTH(AN$1)<MONTH(TODAY()),(DAY(TODAY())= ROW())),AN3,0)+IF(AND(MONTH(AT$1)<MONTH(TODAY()),( DAY(TODAY())=ROW())),AT3,0)+IF(AND(MONTH(AZ$1)<MO NTH(TODAY()),(DAY(TODAY())=ROW())),AZ3,0)+IF(AND( MONTH(BF$1)<MONTH(TODAY()),(DAY(TODAY())=ROW())), BF3,0)+IF(AND(MONTH(BL$1)<MONTH(TODAY()),(DAY(TODA Y())=ROW())),BL3,0)+IF(AND(MONTH(BR$1)<MONTH(TODA Y()),(DAY(TODAY())=ROW())),BR3,0)+IF(AND(MONTH(BX $1)<MONTH(TODAY()),(DAY(TODAY())=ROW())),BX3,0)+I F(AND(MONTH(CD$1)<MONTH(TODAY()),(DAY(TODAY())=RO W())),CD3,0)+IF(AND(MONTH(CJ$1)<MONTH(TODAY()),(DA Y(TODAY())=ROW())),CJ3,0)+IF(AND(MONTH(CP$1)<MONT H(TODAY()),(DAY(TODAY())=ROW())),CP3,0)+IF(AND(MO NTH(CU$1)<MONTH(TODAY()),(DAY(TODAY())=ROW())),CU 3,0)+IF(AND(MONTH(DA$1)=MONTH(TODAY()),(DAY(TODAY( ))=ROW())),DA3,0)

you can actually put that into a cell in row 3 and then fill it down to row
15 (and beyond) and it will work. Since you only go to row 15, we don't have
to worry about months with <31 or <30 days, all months have at least 12 days.

As written, there may be an effect you don't want - the values in the
formulas will always be zero after the current day of the month for all
columns. I mean if it is Dec 4, then you'll only see entries in rows 3 and 4
because of the day checks in the formulas. If you need to see all 12 rows
for months Jan-Nov with values for Dec added to rows 3 and 4, then I've got
some additional work to do.

Or maybe what I've done will inspire someone to do some other creative
thinking and come up with a simpler formula that deals with the days and
offsets between columns better. I actually thought about doing this with
OFFSET() and some formula involving the month and day, but haven't delved
into that yet. My gut feeling on that at this point is that it is a 6 of
1/half-dozen of the other kind of deal, with a formula using OFFSET() looking
even uglier.




"SJT" wrote:

I think I'm in need of some type of index & match formula. I have column
headings that are dates and below them are a series of numbers. I would like
a formula that adds the numbers below the column headings based on today's
date. For example, in column AN I have a column heading of 1/1/07 and below
the column heading I have numerical data in rows 3 -15. In column AT I have
the column heading 2/1/07 and in Column AZ, I have the column heading 3/1/07.
This continues in the same pattern for each month of the year and below each
column heading is data in rows 3 - 15. I would like to add the cumulative
total of each row for each column depending on the date. Since today is 3/3,
for example I would like to add the total of Cells AT3 and AN3 in one cell
and then have a similar formula for each of the rows 4-15. On 4/1 this
formula would add to the previous totals the data in Column AZ rows 3 - 15.
Would appreciate any help you could provide. Thank you in advance for your
assistance.