function skips lines

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I have this function and I want it to count all the cells
in the specified range.

=SUMPRODUCT(('Main FOB Log'!K3:K589<>"")*('Main FOB Log'!
N3:N589="No"))

For some reason, it isn't counting N14:N16. I have
retyped the information in those cells many times to make
sure I didn't type in the information wrong. But it still
does not count these cells. Is there some type of
formatting that makes functions skip over certain cells?
What could be causing this problem?

Thank you,

Jennifer
 
Jennifer

Your formula looks fine. Maybe cells K14:K16 are not empty? Or N14:N16 have
a space after No?

Andy.
 
I've already tried all that. There are no spaces, and
cells K14:K16 are full. It apears to be something wrong
with the cell, because even when I delete the cells, my
function count does not change. It won't even count the
cells when I do a COUNTA function. Are you sure there is
nothing in formatting that would change how a function
counts a cell?
Help!
 
I've already tried all that. There are no spaces, and
cells K14:K16 are full. It apears to be something wrong
with the cell, because even when I delete the cells, my
function count does not change. It won't even count the
cells when I do a COUNTA function. Are you sure there is
nothing in formatting that would change how a function
counts a cell?
Click to expand...

There's nothing in formatting that would affect your formula. So the formula

=SUMPRODUCT(('Main FOB Log'!K14:K16<>"")*('Main FOB Log'!N3:N589="No"))

returns zero? And the following formulas evaluate as indicated?

=COUNTIF('Main FOB Log'!K14:K16,"?*") > 0

=COUNTIF('Main FOB Log'!N3:N589,"No") > 0

What do you get for the following array formulas?

='Main FOB Log'!K14:K16<>""

='Main FOB Log'!N14:N16="No"

?
 
Does anyone have an answer for me? I've found a few other
clusters of cells that don't show up in the count. Does
Excel have errors? Is the workbook corrupt? Is there
someway to purge formatting that might cause this
problem? This workbook was existing before I started
working with it. Someone else created it. The workbook
is protected and locked when not in use. Would that have
an affect?
Please answer me!
Thanks.
 
Does anyone have an answer for me? I've found a few other
clusters of cells that don't show up in the count. Does
Excel have errors? Is the workbook corrupt? Is there
someway to purge formatting that might cause this
problem? This workbook was existing before I started
working with it. Someone else created it. The workbook
is protected and locked when not in use. Would that have
an affect?
Please answer me!
Click to expand...

The odds remain overwhelmingly in favor of the hypothesis that there's something
wrong in your data that you haven't found. Formatting has no affect on any
formulas with the sole (obvious) exception of CELL("Format",...). None of the
issue you mention above have any affect on the formula you're trying to use. The
ideal way to debug the problem would be to hide all columns except your columns
K and N and 4 other blank ones to the right of them. For my own convenience I'll
use IS, IT, IU and IV.

Enter these formulas in the topmost row in the Main FOB Log worksheet.

IS3: =K3<>""

IT3: =N3="No"

IU3: =AND(IS3,IT3)

IV3: =--IU3

Are there any rows in which col K and N data appear inconsistent with col IS
through col IV results?
 
-----Original Message-----
...

The odds remain overwhelmingly in favor of the hypothesis that there's something
wrong in your data that you haven't found. Formatting has no affect on any
formulas with the sole (obvious) exception of CELL ("Format",...). None of the
issue you mention above have any affect on the formula you're trying to use. The
ideal way to debug the problem would be to hide all columns except your columns
K and N and 4 other blank ones to the right of them. For my own convenience I'll
use IS, IT, IU and IV.

Enter these formulas in the topmost row in the Main FOB Log worksheet.

IS3: =K3<>""

IT3: =N3="No"

IU3: =AND(IS3,IT3)

IV3: =--IU3

Are there any rows in which col K and N data appear inconsistent with col IS
through col IV results?
Click to expand...


Thanks, but I still have the problem. I did what you
suggested, and I found that column K always came out to be
True, and column N was False in 8 cases. I retyped in the
data. It still came out to be False. The odd thing to me
is that these problem cells seem to be grouped in three
different sets, and they are all "Yes". The count
for "Yes" picks up all the other "Yes", just not these 8.
Any other suggestions before I delete the column and start
over?
 
Thanks, but I still have the problem. I did what you
suggested, and I found that column K always came out to be
True, . . .
Click to expand...

So column K is always nonblank, so these are all presumably correct.
. . . and column N was False in 8 cases. . . .
Click to expand...

Where it shouldn't have been FALSE? I'll assume that's the case, so the problem
lies in column N.
. . . I retyped in the data. It still came out to be False. . . .
Click to expand...

OK, next thing to try for column N is to see if the cell contents are somehow
not what you've entered. If column N should equal "No", then check column N
using =LEN(N3). When column N appears to be "No", does this formula return 2? If
not, then check if =LEN(TRIM(N3)) and =LEN(SUBSTITUTE(N3,CHAR(160),"")) return
2. If so, then something (perhaps Autocorrect) is inserting soft or hard spaces
into your entries.

If LEN(N3) = 2, then check =EXACT(N3,"N"&"o"). If this returns TRUE, then I'd
need to see the file itself because EXACT(N3,"N"&"o") can't be true if N3="No"
is false. However, if EXACT(N3,"N"&"o") is FALSE, then what does the formula
=CODE(N3)&" "&CODE(MID(N3,2,1)) return? If it returns "78 48", then something is
replacing the letter O with zero 0, which is difficult to see. Autocorrect would
again be the prime suspect. If it's not Autocorrect, then you could have nasty
event handlers screwing up entries. Try loading the workbook with macros
disabled.
 
Back
Top