+ Reply to Thread
Results 1 to 2 of 2

SUMIF with embedded text search criteria

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Turkey
    MS-Off Ver
    Mac 2011
    Posts
    3

    SUMIF with embedded text search criteria

    Required
    The ability to sum an array of values if a related cell includes a particular string. In this case the string is "Ferhat".

    My attempt
    ferhat
    Ferhat is a man =SEARCH($D$1,A2) =NOT(ISERR(B2)) 10 =SUMIF($C$2:$D$4,C2=TRUE,$D$2:$D$4)
    A man is Ferhat =SEARCH($D$1,A3) =NOT(ISERR(B3)) 15 =SUMIF($C$2:$D$4,C3=TRUE,$D$2:$D$4)
    He is a man =SEARCH($D$1,A4) =NOT(ISERR(B4)) 222 =SUMIF($C$2:$D$4,C4=TRUE,$D$2:$D$4)

    This produces some interesting/crazy results. NB "ferhat" sits in cell D1. Upper left is cell A1.

    Here the answer I'm looking for is 25 = 10 + 15. However the criterion in my formula is dependent on only one cell. It should evaluate each entry in Column A and if "Ferhat" exists then include the corresponding value in the SUM.

    Argh!

    Thanks
    Mark

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: SUMIF with embedded text search criteria

    Using your posted example...
    This regular formula sums the Col_D value where the corresponding col_A cell contains ferhat
    =SUMIF(A2:A4,"*"&$D$1&"*",D2:D4)

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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