Results 1 to 4 of 4
Thread: Array Formula (2000)

20010627, 01:42 #1
 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)}

20010627, 05:44 #2
 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

20010627, 15:29 #3
 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

20010627, 19:41 #4
 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.