Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using wildcard with SUMPRODUCT (Excel 2003)

    I have the following SUMPRODUCT formula:

    =SUMPRODUCT((Letters!$D$2:$D$3000>=BOM)*(Letters!$ D$2:$D$3000<=EOM)*(LEFT(Letters!$C$2:$C$3000,14)=" ACHLL1 Letters"),Letters!$E$2:$E$3000)

    I would like to use this same type of formula somewhere else in my workbook and can't seem to modify it to meet my needs. How can I change this formula to look on the Letters!$C$2:$C$3000 and look for anything within that contains "can"? This would pull everything that I am looking for in this column (i.e. CANADIAN, CANADA, _Can).

    Thanks for your help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using wildcard with SUMPRODUCT (Excel 2003)

    You can use the SEARCH function for this:

    NOT(ISERROR(SEARCH("can",Letters!$C$2:$C$3000)))

    SEARCH is not case-sensitive (FIND is its case-sensitive counterpart)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcard with SUMPRODUCT (Excel 2003)

    Thanks Hans! That worked like a charm!

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcard with SUMPRODUCT (Excel 2003)

    Hans,

    Is it possible to use this same type of formula to look for things in that column that do not have "can" in them?

    Thanks again!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using wildcard with SUMPRODUCT (Excel 2003)

    Simply omit the NOT( ) around the expression:

    ISERROR(SEARCH("can",Letters!$C$2:$C$3000))

    This returns TRUE if the SEARCH function results in an error, i.e. if the text "can" is not found.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcard with SUMPRODUCT (Excel 2003)

    Thanks again!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •