Array won't read cells

Don C · Feb 25th, 2002, 02:57 PM

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 · Feb 25th, 2002, 03:03 PM

Quote:

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 · Feb 25th, 2002, 03:13 PM

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 · Feb 25th, 2002, 03:21 PM

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 · Feb 26th, 2002, 07:10 AM

=COUNTIF(H2:H100,"Y")

Aladin Akyurek · Feb 26th, 2002, 07:13 AM

Quote:

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 · Feb 26th, 2002, 09:12 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 02:57 PM	#1
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	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

Feb 25th, 2002, 03:13 PM	#3
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	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.

Feb 25th, 2002, 03:21 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,314	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 ]

Feb 26th, 2002, 07:10 AM	#5
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	=COUNTIF(H2:H100,"Y")

Feb 26th, 2002, 09:12 AM	#7
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	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