Counting entries in another column if the first one is blank

[Morfi]

I have a worksheet called Apr-Jun with the following columns:

Column M = date of stage one response
Column S = was deadline met? (entries will be Y or N)
Column X = date of stage two response
Column Z = was deadline met? (entries will be (Y or N)

On a separate worksheet I want to work out how many responses in a certain time period met the deadline ( I have the start and end dates in cells C11 and C12 respectively). If there is an entry in column M that falls within the date range i.e. 01/04/2011-30/04/2011, I want the formula to count all of the Y entries in column S. If there is not an entry in column M, I want it to then look to column X to see if there is an entry in that date range and if so, count the Y's in column Z.

I therefore want it to count the Y in column S if there is an entry that falls within the date range in column M but if column M is blank I want it to look to column X for dates that fall within the range and count the Y's in column Z. If there is a date in columns M and X then I want it to ignore column X.

I hope that makes sense. Can anyone help me with this?

 

[AlphaFrog]

Try something like this...

=IF(SUMPRODUCT((('Apr-Jun'!$M$2:$M$20>=$C$11)*('Apr-Jun'!$M$2:$M$20<=$C$12)),--('Apr-Jun'!$S$2:$S$20="Y"))=0, 
    SUMPRODUCT((('Apr-Jun'!$X$2:$X$20>=$C$11)*('Apr-Jun'!$X$2:$X$20<=$C$12)),--('Apr-Jun'!$Z$2:$Z$20="Y")),
    SUMPRODUCT((('Apr-Jun'!$M$2:$M$20>=$C$11)*('Apr-Jun'!$M$2:$M$20<=$C$12)),--('Apr-Jun'!$S$2:$S$20="Y")))

Note: if you have Excel 2007 or later, you could use COUNTIFS instead of SUMPRODUCT.