Thanks:  0
Likes:  0

1. 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

2. 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.

3. 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.

4. Apply

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

What do we get?

Try also CLEAN instead of TRIM.

What COUNTIF formula did you use:

=COUNTIF(H2:H100,"Y") ?

[ This Message was edited by: Aladin Akyurek on 2002-02-25 14:30 ]

5. =COUNTIF(H2:H100,"Y")

6. 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?

7. 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 (?).

Don

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•