Thank you, Biff. You solved my problem. Although today EDate is working
again. Mystery.

Anyway, S1 contains TEXT(TODAY(),"mmm-yy") for the most part. Then, when
that period has passed but I want to preserve the historical spreadsheet, I
change it to TEXT(DATE(2007,1,1),"mmm-yy").

With the information you provided below, I'm seeing what my problem is. I
appreciate all your help.

"T. Valko" wrote:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

Your formula works just fine for me.

However, it can be reduced to:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(S1-1,"mmm-yy"),$D$1:$O$1,0)))

I'm assuming that cell S1 contains a date in January 2007? I used 1/1/2007
in cell S1. If cell S1 may contain any date in January 2007, like, say,
=TODAY(), then use:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT($S$1-DAY($S$1),"mmm-yy"),$D$1:$O$1,0)))

Biff

"T. Valko" wrote in message
...
I'm going to take a break for a few hours. I'll see what I can up with
when I return.

Biff

"DallasLDY" wrote in message
...
I'm still having a problem. I'm trying to do a conditional sum based on
the
column headings, which are formatted as text(date(2006,12,1),"mmm-yy").
I
put actuals in after closing of each month, so in January 2007, I enter
December actuals. So I want this to add up Jan 06 through Dec 06. My
current formula with EDATE is:

=SUM(D2:INDEX(D2:O2,MATCH(TEXT(DATE(YEAR(EDATE($S$ 1,-1)),MONTH(EDATE($S$1,-1)),1),"mmm-yy"),$d$1:$o$1,0))))

When I substitute your function into mine (starting with Date( ), I get
an
error on the second YEAR portion of your statement. In addition, if the
date
is 12/1/2006, I get a result of 11/1/2007 from your function.

This is frustrating. I even tried an if statement
IF(year(s1)=1,year(s1)-1,year(s1)) but that gives me Dec-05.

Any other ideas?

Leslie

"T. Valko" wrote:

You can use this formula as a replacement for Edate and not have to
"worry"
about the ATP:

=DATE(YEAR(start_date),MONTH(start_date)+months,MI N(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH( start_date)+months+1,0))))

Biff

"DallasLDY" wrote in message
...
I have several spreadsheets and I made changes to a LOT of formulas
using
the
EDate function. I have the Analysis Tool Pack add-in checked in
Excel.
It
all worked fine yesterday.

Today, if I open the ss, I get the #NAME error. I have to go to Add
Ins,
uncheck the Analysis Tool Pack, hit okay, then go back and re-activate
it
in
order to make my formula calculate. I save and close the spreadsheet,
and
I
open it again with the same error.

Is this a bug or does anyone know what is going on? Please help. I
have
used this function in about 200 ss's in about 50 fields per ss!

These ss's are shared, and I've asked everyone who uses them to
activate
the
add-in. Can you help me?