+ Reply to Thread
Results 1 to 7 of 7

SUMIF with wildcard

  1. #1
    Registered User
    Join Date
    01-26-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    SUMIF with wildcard

    Hello all,

    I am trying to sum based on wildcard criteria (please see attached).
    I tried few attempts yet neither worked.
    Could you please advise?

    Thank you and best regards.

    Capture.PNG

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,258

    Re: SUMIF with wildcard

    Hi and welcome
    without the sheet to work on try something along he lines of =SUMPRODUCT(--(isnumber(search(e6,A3:A5))),B3:B5) (1st attempt 5)
    Last edited by Pepe Le Mokko; 01-26-2019 at 10:45 AM. Reason: removed wildcards

  3. #3
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,799

    Re: SUMIF with wildcard

    Please try
    =SUMPRODUCT(SUMIFS($B$2:$B$8,$A$2:$A$8,IFERROR(--(F2&{"","*"}),"*"&F2&"*")))

    or similar to Pepe's

    =SUMPRODUCT(ISNUMBER(FIND(F2,A2:A9))*B2:B9)

  4. #4
    Registered User
    Join Date
    01-26-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: SUMIF with wildcard

    Hi,
    Thank you very much and sorry I couldn't upload the workbook (think is a bug).
    Your solution worked perfectly.
    Still I don't understand why the wildcard is not picking all values in SUMIF function.
    It's either approximate match (Attempt 1) or exact match (Attempt 2).
    Even when i tried to combine those 2 results with SUMIFS it gave null result (Aattempt 3).
    Could you please tell where I was wrong?

    Capture.PNG

    Thank you and best regards,

  5. #5
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,258

    Re: SUMIF with wildcard

    Just be aware that FIND is case sensitive

  6. #6
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,799

    Re: SUMIF with wildcard

    Wildcard only applies for Text, 999 in B4 is number.
    attempt 3 is for AND condition, A2:A8 is number 999 and text *999*, it will never be True for both.


    Pepe, Thanks. Dice use number, so I use Find, 2 letters shorter .
    Last edited by Bo_Ry; 01-26-2019 at 11:00 AM. Reason: Typo

  7. #7
    Registered User
    Join Date
    01-26-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: SUMIF with wildcard

    Thank you all for prompt solution and explanation.
    Best regards

+ 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