DATE formula help
Posted by Stu on May 10, 2001 6:41 PM
I am attampting to add (SUM) the dollar values in column I, if the dates in column L are less than 12 months from today's date, AND the corresponding text in column D is "A", "B", "C", OR "D". I have written an array formula that will do the above if the date is less than 1/1/2002, but I want this answer to be able to change as the current date changes.
My current formula is:
{SUM(IF(L7:L56,1/1/2,1,0)*(IF(D7:D56="A",1,0)+IF(D7:D56="B",1,0)+IF(D7:D56="C",1,0)+IF(D7:D56="C",1,0)*I7:I56)}
Thanks in advance for your help.
Posted by Kevin James on May 10, 2001 7:41 PM
Hi Stu:
Change the fixed date to Today()
Posted by Aladin Akyurek on May 10, 2001 9:26 PM
Stu & Kevin: This could be better...
=SUM((EDATE(L7:L56,12)=TODAY())*(D7:D56={"A","B","C","D"})*(I7:I56))
Aladin
Posted by Aladin Akyurek on May 10, 2001 9:28 PM
Make that part: ...<=TODAY()...
Posted by Aladin Akyurek on May 10, 2001 9:55 PM
Re: Make that part: ...<=TODAY()...
Another Omission (not yet fully awake, I guess):
Now, it should be OK.
=SUM((EDATE(L7:L56,12)<=TODAY())*(OR(D7:D56={"A","B","C","D"}))*(I7:I56))
Posted by Stu on May 11, 2001 5:24 AM
It doesn't seem to be working
Aladin,
The above formula is returning "#NAME?" as an answer. Any suggestions?
Thanks,
Stu
Posted by Aladin Akyurek on May 11, 2001 5:45 AM
Re: It doesn't seem to be working
Stu: You need to add Analysis Toolpak via Tools|Add ins.
Aladin
Posted by Stu on May 11, 2001 6:31 AM
Re: Still not working
Aladin,
I added the Analysis Toolpak and theAnalysis Toolpak-VBA via Tools|Add ins. The formula is still returning "#NAME?" . Any more suggestions would be appreciated.
Stu
Posted by Stu on May 11, 2001 7:05 AM
Re: correction to my previous reply
The formula is now returning "#VALUE!". Here is my exact formula:
=SUM((EDATE(L7:L56,12)<=TODAY())*(OR(D7:D56={"AC/HQ","AC/GTS","AC/INF","AC/ADV"}))*(I7:I56))
Posted by Aladin Akyurek on May 11, 2001 7:56 AM
Re: correction to my previous reply
Stu,
EDATE seems to have a problem in array-formulas. Try the following formula instead:
=SUM((DATEDIF(L7:L56,TODAY(),"M")<=12)*(OR(D7:D56 ={"AC/HQ","AC/GTS","AC/INF","AC/ADV"}))*(I7:I56))
Aladin
Posted by Stu on May 11, 2001 8:02 AM
Re: the result is now "#NUM!"
Posted by Aladin Akyurek on May 11, 2001 8:20 AM
Re: the result is now "#NUM!"
Stu,
Maybe there is a problem with the date values in L7:L56. I have been able to get #NUM! with a negative number in this range. Also an error value such as #NUM! in I7:I56.
Can you post 10 values from each relevant range.
Aladin
Posted by Stu on May 11, 2001 8:32 AM
Re: here you go
10 values from Column D:
prec/g
AC/HQ
AC/HQ
AC/HQ
AC/HQ
AC/GTS
SSP
prec/g
AC/GTS
SSP
10 Values from Column I:
$350,000
$101,000
$65,000
$5,000
$63,000
$89,000
$65,000
$61,000
$17,300
$272,000
10 values from column L
5/3/2001
7/3/2001
5/2/2001
5/2/2001
12/4/2001
5/2/2001
12/1/2000
this cell is blank
2/2/2001
Posted by Aladin Akyurek on May 11, 2001 9:23 AM
Re: here you go
For the sample data, I get $690,300 , which seems to be correct.
Would you apply the following array-formula to your each data range?
=SUM(ISERROR(L7:L56)*1)
Aladin
,
Posted by Stu on May 11, 2001 10:30 AM
Please clarify...
Sorry, but I'm not sure what you mean by "each data range"
Stu
Posted by Aladin Akyurek on May 11, 2001 10:53 AM
Re: Please clarify...
All this was to see whet ther you have an error value somewhere in L7:L56, D7:D56, or I7:I56. Probably not. You can nevertheless check
with =SUM(ISERROR(L7:L56)*1), =SUM(ISERROR(D7:D56)*1), and =SUM(ISERROR(I7:I56)*1).
Another possibilty is the date format.
If you want to, you can email the (part) of your workbook this troubles arises.
Aladin
Posted by Stu on May 11, 2001 11:04 AM
I just emailed you. nm
Posted by Aladin Akyurek on May 11, 2001 12:29 PM
At last...
Stu,
The formula has to be extended because of dates beyond today (way in the future):
=SUM((IF(L7:L56<=TODAY(),DATEDIF(L7:L56,TODAY(),"M")<=12))*(OR(D7:D56 ={"AC/HQ","AC/GTS","AC/INF","AC/ADV"}))*(I7:I56))
The clue is that DATEDIF must be feeded the smallest date as its first arg. In your case with dates in the future we have to amend the formula to cope the situation as suggested above.
What a painful discovery for both of us.
Cheers.
Aladin
