Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Sumif wildcard

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumif wildcard

    Hi, new here. Been scratching my head around this for while.

    I am currently using a =sum(sumif(A1:A10, {"*text1*","*text2*","*text3*"}, B1:B10)) formula to replace a really messy =sum(vlookup^n)
    =sumif(A1:A10, C1, B1:B10) would work with a reference but I cannot throw in multiple references.
    I am wondering if there is any way I can use cell reference for the criteria array for the sumif function. Everything I've tried so far seems to throw syntax error. Only thing that works is explicit text.
    Since array is throwing wildcard kind of like a query is there a way for me to toss an OR statement in there to concatenate the entire criteria?

    Are there any syntax that would support the following structures:

    =sumif(A1:A10, C1, B1:B10)
    where C1.text = *text1*|*text2*|*text3*

    or

    =sum(sumif(A1:A10, {C1, C2, C3}, B1:B10))
    where C#.text = *text#*

    I know I could simply hard code the criteria in or use multiple sumif or sumifs with cell reference, but that may still end up too messy and I would not be able to use dynamic range / range sizes for this method.
    The only other solution I can think of is to bandage this with is a macro to populate the formulas.

    plssendhelp

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,205
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumif wildcard help

    If each cell value includes the wildcard characters (ie. *text1*), try...

    =SUMPRODUCT(SUMIF(A1:A10,C1:C3,B1:B10))

    If each cell value doesn't include the wildcard characters (ie. text1), try...

    =SUMPRODUCT(SUMIF(A1:A10,"*"&C1:C3&"*",B1:B10))

    Hope this helps!

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif wildcard help

    Quote Originally Posted by Domenic View Post
    If each cell value includes the wildcard characters (ie. *text1*), try...

    =SUMPRODUCT(SUMIF(A1:A10,C1:C3,B1:B10))

    If each cell value doesn't include the wildcard characters (ie. text1), try...

    =SUMPRODUCT(SUMIF(A1:A10,"*"&C1:C3&"*",B1:B10))

    Hope this helps!
    Thank you, I didn't realize sumproduct can be used like that.

    However, I realized an inherent problem with the sum and sumif. Unions in those sets are counted twice so when I passed in "*" as one of the sumif it gave me a number greater than the sum of all return column.
    This means even explicit declaration of the array like {"*text1*","*text2*","*text3*"} wouldn't work since it works like a loop of sumif instead of just a single query of multiple OR statements.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,205
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumif wildcard help

    Can you post a small sample of the data, the criteria involved, and the expected result?
    Last edited by Domenic; May 27th, 2018 at 12:31 AM.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif wildcard help

    Sub-Category Product Name Sales Quantity Discount Profit
    Bookcases Bush Somerset Collection Bookcase 261.96 2 0 41.9136
    Chairs Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back 731.94 3 0 219.582
    Labels Self-Adhesive Address Labels for Typewriters by Universal 14.62 2 0 6.8714
    Tables Bretford CR4500 Series Slim Rectangular Table 957.5775 5 0.45 -383.031
    Storage Eldon Fold 'N Roll Cart System 22.368 2 0.2 2.5164
    Furnishings Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood 48.86 7 0 14.1694
    Art Newell 322 7.28 4 0 1.9656
    Phones Mitel 5320 IP Phone VoIP phone 907.152 6 0.2 90.7152
    Binders DXL Angle-View Binders with Locking Rings by Samsill 18.504 3 0.2 5.7825
    Appliances Belkin F5C206VTEL 6 Outlet Surge 114.9 5 0 34.47
    Tables Chromcraft Rectangular Conference Tables 1706.184 9 0.2 85.3092
    Phones Konftel 250 Conference phone - Charcoal black 911.424 4 0.2 68.3568
    Paper Xerox 1967 15.552 3 0.2 5.4432


    Sum of total Column("Quantity") = 55

    ___________________________________________________________________________________________________
    Where H1:H4 = {*one*, *irs*, *, *}

    Desired result:
    1. MagicSumIf(A:A, "*one*|*irs*", D:D) = 13
    2. MagicSumIf(A:A, "*one*|*irs*|*", D:D) = 55
    or
    3. MagicSumIf(A:A, H1:H3, D:D) = 55
    4. MagicSumIf(A:A, H1:H4, D:D) = 55

    Current result:
    1. Sum(SumIf(A:A, H1:H2, D:D) = 13
    2. Sum(SumIf(A:A, H1:H3, D:D) = 68
    3. Sum(SumIf(A:A, H1:H4, D:D) = 123

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,205
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumif wildcard help

    In that case, try...

    Code:
    =IF(ISNUMBER(MATCH("~*",H1:H4,0)),SUM(D:D),SUMPRODUCT(SUMIF(A:A,H1:H4,D:D)))


  7. #7
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif wildcard help

    Quote Originally Posted by Domenic View Post
    In that case, try...

    Code:
    =IF(ISNUMBER(MATCH("~*",H1:H4,0)),SUM(D:D),SUMPRODUCT(SUMIF(A:A,H1:H4,D:D)))

    This is returning double counted entries as long as the sum is not greater than the total sum of column
    Lookup with "Art" and "*rt would return the "Art" Row return value of 4 twice when it should only picked up once if it was a query with "Art"|"*rt" equivalent syntax.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,205
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumif wildcard help

    In that case, try...

    =SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

    ...confirmed with CONTROL+SHIFT+ENTER. Although, if the criteria range (ie. H1:H4) can contain empty cells, try the following instead...

    =SUM(IF(MMULT(IF(LEN(TRANSPOSE(H1:H4))>0,IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),0),ROW(H1:H4)^0)>0,D2:D14))

    ...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif wildcard help

    Quote Originally Posted by Domenic View Post
    In that case, try...

    =SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

    ...confirmed with CONTROL+SHIFT+ENTER. Although, if the criteria range (ie. H1:H4) can contain empty cells, try the following instead...

    =SUM(IF(MMULT(IF(LEN(TRANSPOSE(H1:H4))>0,IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),0),ROW(H1:H4)^0)>0,D2:D14))

    ...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!
    That is a really nice way of using arrays!
    This works for my current application, but the formula is evaluating all lookup values as *text* since search function finds any match instead of exact match with wildcard.

    Is there a way that I can use arrays as toggle but also take advange of wildcard lookup such as the ones in Sumif criteria. Match only throws back the first occurance of each lookup value unfortunately.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,205
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumif wildcard help

    Can you please elaborate, and include some sample data along with the expected result?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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