DATEVALUE Logical Test

[Drewmyster]

Hi there, please can someone help me fix this formula which only returns a FALSE error.

=IF($A:$A<DATEVALUE("01/04/2011"),$B:$B>DATEVALUE("30/04/2011"),COUNTIF($C:$C,">30"))

As you can see, I wish to check data before, and after, a certain date, then count the number of occurances above 30.

Thanks.

 

[Andrew Poulsom]

Try eg:

=SUMPRODUCT(--(A1:A1000 < DATEVALUE("01/04/2011")),--(B1:B1000 > DATEVALUE("30/04/2011")),--(C1:C1000 > 30))

Adjust the range references to suit, but make sure that they all have the same number of rows and avoid using entire columns. If you have Excel 2007 or above you can use the COUNTIFS function.

 

[Drewmyster]

Never Mind, thanks.

 

[Drewmyster]

Hi there, please can someone help me fix this formula which only returns a FALSE error.

=IF($A:$A←DATEVALUE("01/04/2011"),$B:$B→DATEVALUE("30/04/2011"),COUNTIF($C:$C,">30"))

As you can see, I wish to check data before, and after, a certain date, then count the number of occurances above 30.

Thanks.

Sorry, I realised afterwards that the full formula was not shows because this forum won't accept the "<" AND ">" keys, so I've replaced with arrows so you can see the full formula and what it is I'm trying to achieve.

I hope this makes it clearer.

Will the SUMPRODUCT formula you suggested still be able to acheive this?

 

[Andrew Poulsom]

I got your full formula from the HTML code, so I think mine should work for you.