If Statement involving a Date

[Moxioron]

Hello all.

I have looked high and low for an answer on this and i am sure it is right in front of me.

I want to return NA() if the date value in AB2 is less than the date values in A2:A50000. With the formula below I get a #VALUE! result.

Where am I going wrong? Thank you.


=IF(Data!$A$2:$A$50000>DATEVALUE(AB2),NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))

 

[Moxioron]

It worked...kind of.

For some reason it ignores the =. So if Data!A2:A50000 is 08/01/2012 and AB2=08/01/2012 I still get the #N/A. However, if I change the date in A2:A50000 to 08/02/2012, then the sumproduct kicks in.


Weird. Thanks for your help.

 

[barry houdini]

In that case I think you want to remove that =, i.e.

=IF(MAX(Data!$A$2:$A$50000)< AB$2,NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),Data!$E$2:$E$50000))