# sumif based on the last 2 characters of corresponding cell in another column

• Hi all,
I'm trying to write a sumif formula. i want to sum the values from J6:J25 ONLY if the right 2 characters in D6:D25 <> ".2".
this is what i've come up with, but it doesn't work

=SUMIF(D6:D25,RIGHT((D6:D25),2)<>".2",J6:J25)

any help on this is greatly appreciated.
thanks
Greg

## Files

• Re: sumif based on the last 2 characters of corresponding cell in another column

Column-B is empty in the sample workbook. Adjust references in this formula - it is based on the workbook

=SUMPRODUCT(--(RIGHT(\$D\$3:\$D\$17,2)<>".2")*(\$H\$3:\$H\$17))

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: sumif based on the last 2 characters of corresponding cell in another column

AAE:
thanks, i saw the typo, in my post, and corrected it, but not before you saw it. Your solution gives me a #value error, but because i want to total column J, which are products of a formula, i need to incorporate error checking into your solution for it to work.
as i am just a beginner at this, what would you propose?
thanks
also, what does the -- do in the formula you provided? (i tried to search for this but i need some explanation to point me in the right direction)
greg

• Re: sumif based on the last 2 characters of corresponding cell in another column

The #value error is not cause by my formula it is caused by your formula in J-cells, where you have a blank being returned. Excel does not see this as blank, but as text and you can't add text and numbers.
Change this
=IF(H3="","",H3*I3)

to this
=IF(H3="",0,H3*I3)

SUMPRODUCT Explained

More on SUMPRODUCT

You can also visit YOUTUBE for demos. Search on SUMPRODUCT.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: sumif based on the last 2 characters of corresponding cell in another column

gbs65,

Cross posting without providing a link to the other post is a violation of the forum rules.
You cross posted at Excel Help Forum

Please read Message to Cross Posters, accessed via the link in my signature, to understand why this is not tolerated on any Excel Forum.
Future violations of this rule will result in your thread being deleted or locked without notice.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: sumif based on the last 2 characters of corresponding cell in another column

AAE:
My bad, i meant to add the link when i edited my first post, but got side tracked and forgot.
thanks

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!