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
sumif based on the last 2 characters of corresponding cell in another column



Re: sumif based on the last 2 characters of corresponding cell in another column
ColumnB 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))

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 Jcells, 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)More on SUMPRODUCT
You can also visit YOUTUBE for demos. Search on SUMPRODUCT.

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 ForumPlease 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. 

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 
Re: sumif based on the last 2 characters of corresponding cell in another column
At Ozgrid we ask that members do not add [Solved] or similar to threads, because it makes it harder to search for threads associated with the Solver function of Excel.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!