Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formula (2000)

    Formula is supposed to sum the numbers in cells B1:B4, where the text in cells A1:A4 ends in "A". What's wrong with it?
    {=sumif($A$1:$A$4,RIGHT($A$1:$A$4)="A",B1:B4)}

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula (2000)

    I would use {=SUM((RIGHT($A$1:$A$4,1)="A")*(B1:B4))} , though using {=SUM((RIGHT($A$1:$A$4)="A")*(B1:B4))} also works.

    Brooke

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Array Formula (2000)

    If you are trying to enter this as an array formula, you have a bracket misplaced: You have
    <pre>{=sumif($A$1:$A$4,RIGHT($A$1:$A$4)="A",B1:B4) }
    </pre>

    While I just entered:
    <pre>{=SUM(IF(RIGHT(A2:A5,1)="A",B2:B5,0))}
    </pre>

    which works. Note the left bracket between "SUM" and "IF" - your syntax is invoking the "SUMIF" function.
    I don't think SUMIF has the facility to evaluate substrings, although I don't know since I always use the array formula "sum(if(..." approach. If it is SUMIF you are looking for, I don't believe that has to be entered as an array formula

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula (2000)

    Thanks much to both. It would appear I cleverly avoided the simpler method.

Posting Permissions

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