Sumif on last digit

[Ted Metro]

Sorry --

I accidentally hit a key and it posted.

I am using the array formula below, but I wonder if it can be done with the
Sumif formula.

=SUM((RIGHT(a1:a4,1)="1")*b1:b4)

I have a list of accounts and their values

101 400
501 100
302 150
405 225

It doesn't work when I do a sumif(a1:a4,"*1",b1:b4) even if I change the
format on column A to text.

Is there an easy way with just the sumif formula and no array"

 

[T. Valko]

Don't even "mess around" with changing the format in order to get a formula
to work.

Try this:

=SUMPRODUCT(--(RIGHT(A1:A4)="1"),B1:B4)

Biff

 

[Bernard Liengme]

Or if you really want to upset grammarians who hate double negatives:
=SUMPRODUCT(--(--RIGHT(A1:A4)=1),B1:B4)
best wishes

 

[Ted Metro]

Thanks T. Valko -- you are the man!

Don't even "mess around" with changing the format in order to get a formula
to work.

Try this:

=SUMPRODUCT(--(RIGHT(A1:A4)="1"),B1:B4)

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks T. Valko -- you are the man!