Double counting in SUMPRODUCT formula

  • Re: Double counting in SUMPRODUCT formula


    Hello,


    With your own array formula ... (use Control+Shift+Enter ... instead of Enter )


    Code
    = SUMIFS(Value, Description, "*"& C16:C17 &"*")


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Double counting in SUMPRODUCT formula


    Hi Carim. And thanks for the reply.
    Your suggested method do eliminate the "double counting". However, it presents another challenge: it only counts the items that contain both strings... ignoring those that contain at least one of them.

  • Re: Double counting in SUMPRODUCT formula


    You are right ... Probably because I read your title ... :wink:


    Do you mean all your criteria are independent ... and you need OR ... instead of AND ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Double counting in SUMPRODUCT formula


    Quote from Luis Ah-Hoy Jr.;788062

    Exactly.


    If you do mean OR between all your criteria ... you will just end up ... with the Sum ...


    Think you should clarify exactly the relationship between your criteria ...


    Meanwhile for cell E22 ... you could test


    Code
    =SUMPRODUCT(--NOT(ISNUMBER(SEARCH(C21,$B$6:$B$11))),--NOT(ISNUMBER(SEARCH(C22,$B$6:$B$11)))*(Value))


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Double counting in SUMPRODUCT formula


    Quote from Carim;788063

    Meanwhile for cell E22 ... you could test


    Code
    =SUMPRODUCT(--NOT(ISNUMBER(SEARCH(C21,$B$6:$B$11))),--NOT(ISNUMBER(SEARCH(C22,$B$6:$B$11)))*(Value))


    HTH


    For now, let's just focus on adjusting the formula for the first two criteria (C16 and C17) and leave the "exclusions" for later. This exercise is very complicated already. ;)



    Quote from Carim;788063

    If you do mean OR between all your criteria ... you will just end up ... with the Sum ...


    Yes, it will be a sum.
    But the idea is more complex than that. That's because the sum has to be based on a range of criteria, in order to count all the items containing any of the strings in the criteria range.


    Hope that was clear enough.

  • Re: Double counting in SUMPRODUCT formula


    Those worked, Carim. At least for the current requirements of the spreadsheet.


    But as you may have noticed, the table in my spreadsheet is extremely simplified... and so is the criteria part.
    Meaning that in the long run, I'll have dozen more criteria ranges to deal with, so the formula has to be as versatile as possible.


    So I tried to tweak your formula, in order to accommodate a third criteria ("Description" must contain: "Forklift").
    Since the item that contains the word "forklift" does not meet one of the criteria, more specifically the Analytical Center criteria (6512, instead of 6511) the result should stay at the original 2550. But instead, the tweaked formula returns 3650.
    In other words, the item containing "forklift" was accepted in the sum... when it shouldn't have been.



    Here is the tweaked formula (in red, the only modification made to it):
    =SUMPRODUCT((Depr_Account=C19)*(Analytical_C=C18)*--ISNUMBER(SEARCH(C16;Description))+--ISNUMBER(SEARCH(D17;Description))+(--ISNUMBER(SEARCH(C17;Description))*--NOT(ISNUMBER(SEARCH(C16;$B$6:$B$11))));Value)



    It seems to be well tweaked.
    And if so, why is it returning an incorrect value? (see the attachment)

  • Re: Double counting in SUMPRODUCT formula


    Quote from Luis Ah-Hoy Jr.;788098


    Those worked, Carim. At least for the current requirements of the spreadsheet.
    .....................................................................................................................................................
    .....................................................................................................................................................
    .....................................................................................................................................................


    You are welcome

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

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