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

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

    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.

    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.

    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!

Register Yourself Login