+ Reply to Thread
Results 1 to 6 of 6

SUMIF with multiple criteria, one criteria a specific string

  1. #1
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114

    SUMIF with multiple criteria, one criteria a specific string

    So I've trying to create a formula that will mimic a sumif function but using multiple criteria. One of the criteria is to look for a certain character string that could be inside of a cell that contains other characters as well. I know if I do a sumif function I can use a formula like this:

    =SUMIF(Sheet1!$B:$B,"*" & "Test" & "*",Sheet1!$F:$F)

    That would look in row B for any cells that had the string Test anywhere inside the cell and then sum up column F. My problem is that I need a formula that will look for a certain string as well as make sure it matches another value. I've tried using this formula:

    =SUMPRODUCT(--(Sheet1!B1:B100="*" & "Test" & "*"),--(Sheet1!C1:C100=20),Sheet1!F1:F100)

    That is looking in column B for a string of Test and then also making sure that column C = 20. Fopr any matches it sums up column F. The formula doesn't work though because it doesn't allow the function I have setup to look for Test. Can anyone help me with a solution. Thanks

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524
    I am sure it can be done, your formula looks wrong, can you supply a sample workbook for somebody to work with?

  3. #3
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114
    I've attached the file. There are two sheets in the workbook. The first has the raw data while the second is the reporting page with the formula's. The first criteria is all listed in Row 1 while the second criteria is listed in Column A.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387
    Weasel,

    In Sheet3,
    cell B2 (copied down):
    =SUMPRODUCT((LEFT(Sheet1!$A$1:$A$9657,5)=$B$1)*(Sheet1!$B$1:$B$9657=$A2)*(Sheet1!$C$1:$C$9657))

    cell C2 (copied down):
    =SUMPRODUCT((LEFT(Sheet1!$A$1:$A$9657,5)=$C$1)*(Sheet1!$B$1:$B$9657=$A2)*(Sheet1!$C$1:$C$9657))


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    SumProduct using wildcards

    Try this for B2,
    = SUMPRODUCT(ISNUMBER(SEARCH($B$1,Sheet1!$A$1:$A$17,1))*(Sheet1!$B$1:$B$17=$A2)*(Sheet1!$C$1:$C$17))
    C2 would be
    = SUMPRODUCT(ISNUMBER(SEARCH($C$1,Sheet1!$A$1:$A$17,1))*(Sheet1!$B$1:$B$17=$A2)*(Sheet1!$C$1:$C$17))
    Here is your workbook with the new formulas.
    SumproductWildCards.xls

  6. #6
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114
    That worked but I ran into one problem. In the file I'm working on there are cells that contain numbers and other cells that contain text. When I use the formula use listed it won't work unless I delete all of the text cells in column F. Is there a way to tweak the formula so it would work with text valued cells still in column F?

    Also, if I wanted to change the second criteria (i.e. Sheet1!$B$1:$B$17=$A2) so that instead of looking for a specific value listed in cell A2 it looked for a value range. For example, instead of looking for a match of the value 85 it looked for a match in which the value could be between 80 and 90.

    Thanks in advance for any help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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