View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme Bernard Liengme is offline
external usenet poster
 
Posts: 4,393
Default Sumif on last digit

Or if you really want to upset grammarians who hate double negatives:
=SUMPRODUCT(--(--RIGHT(A1:A4)=1),B1:B4)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"T. Valko" wrote in message
...
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

"Ted Metro" wrote in message
...
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"