Array Formula (2000)

Topic

New Reply

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)}

Reply | Quote

Replies

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

Reply | Quote

If you are trying to enter this as an array formula, you have a bracket misplaced: You have

{=sumif($A$1:$A$4,RIGHT($A$1:$A$4)="A",B1:B4)} 

While I just entered:

{=SUM(IF(RIGHT(A2:A5,1)="A",B2:B5,0))}

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

Reply | Quote

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

Reply | Quote