Problem with #NAME?

[OTT]

I have a formula to calculate entries in column N. The cells in Column N have the following formula in them:

=IF(K2="","",IF(M2<=15,"Y",IF(M2>=16,"N","")))

I want my formula to calculate the entries in column N that contain a Y and am using the following formula:

=SUMPRODUCT(--('July-September 2011'!$K$2:$K$19241>=$C$11),--('July-September 2011'!$K$2:$K$19241<=$C$12),--('July-September 2011'!$N$2:$N$19241="Y"))

It keeps returning #NAME?. I have filtered column N to find the entries that have #NAME? in them but they show as blank rather than as having #NAME? in them. There are no entries in any other row for these cells. I have tried deleting the rows, deleting the above formula from the cells in the column and replacing the formula with another entry but #NAME? still appears as being an entry in the column.

Can anyone help with this please?

 

[Oaktree]

If there's no #NAME error in column N, there likely is one (or more) in column K, in C11, or in C12...

 

[OTT]

Thanks. I checked but there is no other column with that in it. Even when I filter the column to highlight those that contain it, the cells are empty. They don't actually say #NAME? in them.

The spreadsheet is shared and password protected. Could that have anything to do with the problem or with my attempts to fix it not working?

 

[Oaktree]

What do

=SUM('July-September 2011'!$K$2:$K$19241)

and

=SUM('July-September 2011'!$N$2:$N$19241)

return?

 

[OTT]

The first one returns 104988006. The second one returns #NAME?

 

[Oaktree]

There's a #NAME error in column N. Maybe you have hidden rows that's preventing you from seeing it?

Try selecting column N, pressing F5 (for GoTo) --> special --> formulas, checking only the errors box, and clicking OK to find your error.

 

[Rick Rothstein]

The first one returns 104988006. The second one returns #NAME?

Column N is where your problem appears to be. Show us the formula in N2 (which I assume is copied down). Also, what version of Excel are you using?

 

[Rick Rothstein]

Column N is where your problem appears to be. Show us the formula in N2 (which I assume is copied down). Also, what version of Excel are you using?

Ignore my previous message (except for the version question maybe) as I see you told us that in the original message. If you workbook does not contain sensitive information, can you post a copy of it to one of the free file sharing websites on the Internet so we can "play around" with it? If yes, you can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[OTT]

Thanks for all of your suggestions. I only managed to look at this again this morning and using Go to, Special and formulas has helped me solve the problem. There was one cell with an error in it.

Thanks again for your help.