I am trying to exclude the word PPC from these results and am mixing a few items to get it. It returns the correct number for PPC contracts here, but I want it to exclude anything with PPC in cell G3-G. I understand that <> can be used for does not equal but I've tried inserting that multiple places in this ISNUMBER/SEARCH formula and can't figure it out. Is there a better way to get the same answer with a different formula?

The piece I need help with: (ISNUMBER(SEARCH("PPC", Retention!$G$3:$G)))

The full formula:

=SUMPRODUCT((--(MONTH(Retention!$E$3:$E)=MONTH(S$1))*(YEAR(Retention!$E$3:$E)=YEAR(S$1))*(Retention!$D$3:$D="Sales - USA")*(ISNUMBER(SEARCH("PPC", Retention!$G$3:$G)))*(Retention!$H$3:$H="")))


  • 1
    Can you add some sample data so we understand the range of how "PPC" can appear in those cells? Also, it isn't clear exactly what issue you're having. If you just can't figure out how to get the ones that don't contain PPC, use ISERROR instead of ISNUMBER. – fixer1234 May 15 at 22:59
  • @fixer1234 that actually is the answer! I needed it to not container PPC, using ISERROR seemed to fix that! If you offer your comment as an answer below, I'll accept it. Thank you! – Justin Hammond May 16 at 14:34

SEARCH looks for target text in a string. If it finds it, it returns a position number, so wrapping SEARCH with ISNUMBER tests whether it found it.

If SEARCH doesn't find it, it returns an error. So wrapping SEARCH with ISERROR tests whether the text was not found. In your formula, replacing ISNUMBER with ISERROR will include only records that do not contain "PPC".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.