How do I create an Excel 2003 array formula using date criteria ?

Tocogroup
Tocogroup used Ask the Experts™
on
Dear All,
I have created a multiple criteria array formula to count the number of cells with a particular date in one range and a value in another range. Is there something about dates in array formulas that I should know, because it doesn't seem to recognise the criteria ?
Appreciate your help in anticipation.
Please find the worksheet attached. Date-criteria-enquiry.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
I suggest you use the date function:


=SUM(IF(B4:B15=DATE(2010,9,3),IF(C4:C15>100,1,0)))


Peter KwanAnalyst Programmer

Commented:
Please try:

={COUNT(IF(B4:B15=VALUE("03/09/2010"),IF(C4:C15>100,C4:C15)))}
Peter KwanAnalyst Programmer

Commented:
Sorry, typo. Should be:


{=COUNT(IF(B4:B15=VALUE("03/09/2010"),IF(C4:C15>100,C4:C15)))}

Author

Commented:
Thanks guys. I like the non-array formula version and will start using the DATE function in my formulas in future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial