Array won't read cells
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Array won't read cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =COUNTIF(H2:H100,"Y")

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com