You could put the date of interest in a cell somewhere, eg D1, then
you could do this:
=SUMPRODUCT((A1:A12=D1)*(B1:B12="Critical"))
Or, if you want to stick with your own approach, you would need to do
it like this:
=SUMPRODUCT((A1:A12=--"9/17/2008")*(B1:B12="Critical"))
The -- converts the text date to a proper date. Another way is:
=SUMPRODUCT((A1:A12=DATE(2008,9,17))*(B1:B12="Critical"))
The drawback with these last two is that the date of interest is coded
within the formula, so you would have to amend the formula if you
wanted to know the result for a different date, whereas with the first
formula you can just change the date in D1. You could also put the
other criteria in a different cell in the same way.
Hope this helps.
Pete