Array won't read cells

[Don C]

I have a column in which are blanks or the letter "Y" (indicating membership in a group). I can count the Y's with a countif, so I know the entries are clean.

An array formula does not see the Y's however. A simple array, =sum(H2:H100="Y") gives me a zero.

Probably related to this is the fact that a sort on a user defined list will not work either.

Somehow, even though =code(H2) gives me an ascii code of 89, which I think is the correct code for "Y", and countif sees the letters, arrays and sorts don't.

I've tried trims and cleans to no avail.

What else might I do to get this column to be recognized?

TIA

 

[Aladin Akyurek]

On 2002-02-25 13:57, Don C wrote:
I have a column in which are blanks or the letter "Y" (indicating membership in a group). I can count the Y's with a countif, so I know the entries are clean.

An array formula does not see the Y's however. A simple array, =sum(H2:H100="Y") gives me a zero.

Probably related to this is the fact that a sort on a user defined list will not work either.

Somehow, even though =code(H2) gives me an ascii code of 89, which I think is the correct code for "Y", and countif sees the letters, arrays and sorts don't.

I've tried trims and cleans to no avail.

What else might I do to get this column to be recognized?

TIA

Don,

Array-enter:

=SUM((H2:H100="Y")+0)

or enter normally

=SUMPRODUCT((H2:H100="Y")+0)

Note. There is no need for these formulas for a single-condition counting -- COUNTIF is just right for that.

Aladin

 

[Don C]

Actually, I have a multiple criterion array that is not working. To problem solve what is failing, I tried just counting one segment (this one column) and it is failing to count.

It is my hope that once I find out why the array won't work on ONE criterion, I'll be able to fix it to work in the "real" multiple criterion situation.

 

[Aladin Akyurek]

Apply

=SUMPRODUCT((TRIM(H2:H100)="Y")+0)

What do we get?

Try also CLEAN instead of TRIM.

Addendum:

What COUNTIF formula did you use:

=COUNTIF(H2:H100,"Y") ?
This message was edited by Aladin Akyurek on 2002-02-25 14:30

 

[Don C]

=COUNTIF(H2:H100,"Y")

 

[Aladin Akyurek]

On 2002-02-26 06:10, Don C wrote:
=COUNTIF(H2:H100,"Y")

Don,

I'm really grown curious about that range. Is it possible that you sent me a copy of the worksheet trimmed down just to that range?

Aladin

 

[Don C]

A followup:

I sent a sample of the file to Aladin (who must never go to sleep!) who corrected my placement of parentheses. In the suggested formulas, () need to encompass the +0, which I was not doing.

The "+0" is new to me as well. He calls it coercion, apparently forcing the formula to work in values (?).

Boy is this board (and Aladin in particular) helpful!

Don