Formula not working

[Morfi]

I am using the following formula in my spreadsheet to count the number of letters issued over a period of time that met a deadline:

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

M = Date letter issued
C11 = Start Date
C12 = End date
S = Deadline met?

The formula above works fine but when I change the number range of cells in column M and S from 20 to 2000, the formula comes up with #VALUE. Can anyone tell me why that is and what I can do to fix it please?

 

[AlphaFrog]

Do you have any #VALUE errors in Apr-Jun'!$M$21:$M$2000 ?
Or in Apr-Jun'!$S$21:$S$2000 ?

Make sure all the ranges in the formula refer to the same rows. If one range is off, you will get a #VALUE error e.g
=SUMPRODUCT((('Apr-Jun'!$M$2:$M$2000>=$C$11)*( 'Apr-Jun'!$M$2:$M$2000<=$C$12)),--('Apr-Jun'!$S$2:$S$200="Y"))

 

[Andrew Poulsom]

Are you sure that each of the ranges have the same number of rows?

 

[Morfi]

I had a #Value error in one of my columns that I hadn't noticed. That's it working now. Thanks for that!