what's wrong with this formula?

[Harvey Waxman]

{=SUMIF(G18:G17800,"",K18:K17800)}

I get a #VALUE! error

When I sort on column G I get the right value.

Any idea why?

 

[Harvey Waxman]

First, I don't think SUMIF needs to be (or should be) array entered. Try it
with zero as the middle variable.

Can't do that since zero is a valid number in the column. It must find
only blank cells.

 

[Carl Witthoft]

Can't do that since zero is a valid number in the column. It must find
only blank cells.

your formula, as an array formula or not, works fine for me.

I'm 99.44% positive your problem is an invalid value somewhere in column
G.

I have no idea what you mean by "sort on G"

 

[Harvey Waxman]

your formula, as an array formula or not, works fine for me.

I'm 99.44% positive your problem is an invalid value somewhere in column
G.

I have no idea what you mean by "sort on G"

If I sort column G so all the non-blank cells are together, the formula
is ok. If I sort on another column so the non-blank cells are not
contiguous the formula fails.

The cells have conditional formats and drop-down list limiting input but
they can be left blank.

 

[Bob Greenblatt]

If I sort column G so all the non-blank cells are together, the formula
is ok. If I sort on another column so the non-blank cells are not
contiguous the formula fails.

The cells have conditional formats and drop-down list limiting input but
they can be left blank.

Harvey,

I have a sneaking hunch about this. When you sort the column, how many zero
values are there? Does the following formula work?
=sum((len(g18:g17800)=0)*k18:k17800)

Array enter this. What is the result?

 

[Harvey Waxman]

Harvey,

I have a sneaking hunch about this. When you sort the column, how many zero
values are there? Does the following formula work?
=sum((len(g18:g17800)=0)*k18:k17800)

Array enter this. What is the result?

Unsorted I get the same error. But here's something else I noticed.
The "calculate" prompt remains when I get the error but disappears when
I sort and don't get the error. If I cmd= the prompt remains.

BTW this is an interesting variation on the formula to get the
information. Is there some source where I can see all these formulas
with their pro's and con's and examples? It seems to me that sumif's
can be replaced by sum & if or visa versa.

 

[Bob Greenblatt]

Unsorted I get the same error. But here's something else I noticed.
The "calculate" prompt remains when I get the error but disappears when
I sort and don't get the error. If I cmd= the prompt remains.

BTW this is an interesting variation on the formula to get the
information. Is there some source where I can see all these formulas
with their pro's and con's and examples? It seems to me that sumif's
can be replaced by sum & if or visa versa.

Sorry, no source for that. It is a simple array formula. I can't help you
much other than to try Excel help or google groups to see about array
formulas. Does the formula I suggested work?

 

[Harvey Waxman]

Unsorted I get the same error. But here's something else I noticed.
The "calculate" prompt remains when I get the error but disappears when
I sort and don't get the error. If I cmd= the prompt remains.

Does the formula I suggested work?

[/QUOTE]

See above

 

[Harvey Waxman]

See above[/QUOTE]

I think I found the problem. In column G cells were validated with a
list. One cell had something weird in it. The drop-down menu icon
appeared inside the cell itself. when the cell was selected the normal
menu icon appeared next to it.

I could not correct it without copying the data to a new workbook. Your
suspicion was correct, a corrupt cell.

Thanks