Single Search formula for two or more conditions?

  • Hello dear OzGrid!


    I have this formula: =SUMPRODUCT(ISERR(SEARCH({"TRUMP";"WHITE HOUSE"};A2:A21))*N(+C2:E21)) wich should sum all values from C2:E21 wich aren't TRUMP and WHouse, but this isn't working, it only search for TRUMP. N part is for text accuracies if there's any. Maybe SUMPRODUCT can't handle Search.
    This only works like this: =SUMPRODUCT(ISERR(SEARCH("TRUMP";A2:A21))*N(+C2:E21)*ISERR(SEARCH("WHITE HOUSE";A2:A21))) but is there a way to shorten this, would be great! Thanks!

  • Hello,


    In order to fix your issue ... it would be great ... if you would attach a sample file ... :wink:

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

  • Hello,


    Thanks for your workbook .. which does clarify your constraints ... :wink:


    You can test following formula :


    Code
    1. =SUM(IF($A$4:$A$23=$H$1,$C$4:$E$23))+SUM(IF($A$4:$A$23=$I$1,$C$4:$E$23))


    Attached is your revised workbook

    Files

    • Knjiga1N.xlsx

      (10.37 kB, downloaded 59 times, last: )

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

  • Hello,


    Understand your remark ... but it has no impact on result ...


    Attached is your amended file ... :wink:

    Files

    • Knjiga1N.xlsx

      (10.48 kB, downloaded 61 times, last: )

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

  • $A$4:$A$23=$H$1 is not case-sensitive, so should work fine.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • It's ok, but I cannot use helper cells, i must go with Search({"trump","white house"} problem is that it0s isn0t working, actually SUMPRODUCT isn't working :(

  • Help us out here! What are you expecting the answer to be?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Hello again,


    Have you tried the formula provided in message # 4 ... ???


    Have you looked at both your workbooks in messages #4 and #6 ... since they both work fine ...(double-checked with your Autofilter ...)

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

  • Is there a way to short this neat formula: =SUMPRODUCT(ISERR(SEARCH("trump";A2:A21))*N(+C2:E21)*ISERR(SEARCH("white house";A2:A21))) it's giving me sum of all sales wich aren't Trump and White House.


    Why this formula isn't working:
    =SUMPRODUCT(ISERR(SEARCH({"trump","white house"},A2:A21))*N(+C2:E21))


    P.s I forgot, new look is nice, please add some green color :)


  • Is there a way to short this neat formula: =SUMPRODUCT(ISERR(SEARCH("trump";A2:A21))*N(+C2:E21)*ISERR(SEARCH("white house";A2:A21))) it's giving me sum of all sales wich aren't Trump and White House.


    Why this formula isn't working:
    =SUMPRODUCT(ISERR(SEARCH({"trump","white house"},A2:A21))*N(+C2:E21))


    P.s I forgot, new look is nice, please add some green color :)


    Since you have decided to ignore all my messages ...


    Let me wish you the Best of Luck ... :wink:

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

  • Since you have decided to ignore all my messages ...


    Let me wish you the Best of Luck ... :wink:


    Ditto. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules