+ Reply to Thread
Results 1 to 7 of 7

Sumprod formula to exclude string values

  1. #1
    Registered User
    Join Date
    12-17-2010
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Sumprod formula to exclude string values

    Hi there. Hoping to get some help as I can't figure out how to exclude string values. Tried searching on site for answer to my question, but could not find it.

    Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF

    I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.

    Below is the entire folumla just in case. Any help would be greatly appreciated!!!!

    11111111FT04comp
    comp
    tri
    11comp
    11tri
    tri11111111FRP06


    =SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))
    Last edited by gberna2; 01-22-2014 at 03:13 PM.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Sumprod formula to exclude string values

    Hello gberna Can you please upload a sample book with results expected !

    TO attach a book go to advanced --> select paper clip icon OR go to manage attachments
    Last edited by hemesh; 01-22-2014 at 03:36 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    12-17-2010
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sumprod formula to exclude string values

    Thank you, I've uploaded it. Cell D15 in the D-C Tabel tab contains my formula. My expected result when adding in a formula to the existing (which excludes values in col I of the DTCC Submit T+0 tab) is 3. As mentioned previously, I have given column I a name range of DTCCSUBSUPPREF.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Sumprod formula to exclude string values

    Hello gberna 2 Try copy paste below in place of your formula
    =SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)*(--(DTCCSUBINPUTSOURCE<>$I$4))))*(NOT(ISNUMBER(SEARCH("*"&J3&"*",DTCCSUBSUPPREF)))*(NOT(ISNUMBER(SEARCH("*"&J2&"*",DTCCSUBSUPPREF))))))

    search function will search for comp and tri in array and where ever it find tri or comp it will be given the number and other will be evaluated as errors then isnumber function will check the array for numbers and all numbers will be evaluated as true's and errors will be evaluated as false at this stage all true's are for comp and tri and all false means cells doesn't contain tri or comp. So not function will convert all TRUE TO FALSE AND ALL FALSE TO TRUE giving you the result as 3


    If this answer is helpful click the "* add rep icon in the bottom left corner of my post.
    Last edited by hemesh; 01-22-2014 at 04:17 PM.

  5. #5
    Registered User
    Join Date
    12-17-2010
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sumprod formula to exclude string values

    this worked like a charm...after I amended the values in J2, J3 to remove all references to *,<>

    *comp*
    <>*tri*

    Thank you! Will let you know if I encounter any other issues.

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Sumprod formula to exclude string values

    Thanks for the feedback and rep !
    you are always welcome if you have any issues

  7. #7
    Registered User
    Join Date
    12-17-2010
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sumprod formula to exclude string values

    Namaste!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Exclude values within brackets from my formula.
    By faithhammer in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-25-2013, 01:17 AM
  2. sum formula to exclude zero values
    By nmginn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2013, 04:19 PM
  3. [SOLVED] SUMIF, SUMPROD, LEN, LEFT, cond sum checking string's length and initial character cells
    By Dolphin74 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-23-2012, 05:16 AM
  4. Replies: 4
    Last Post: 03-30-2012, 07:55 AM
  5. Excel sumprod formula
    By Engineers08 in forum Excel General
    Replies: 3
    Last Post: 08-10-2007, 09:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1