+ 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 Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,123

    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
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,099

    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 Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,123

    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
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,099

    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)

Similar Threads

  1. [SOLVED] How to SUMIF using a wildcard?
    By OmegaSea in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-01-2015, 04:51 PM
  2. [SOLVED] Sumif with wildcard challenge
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2015, 05:08 PM
  3. [SOLVED] wildcard in sumif(s) or sumproduct
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2014, 11:22 AM
  4. Sumif for wildcard character
    By patam in forum Excel General
    Replies: 2
    Last Post: 01-09-2007, 10:05 AM
  5. Wildcard with sumif
    By fractallinda in forum Excel General
    Replies: 8
    Last Post: 02-27-2006, 07:15 PM
  6. Sumif using wildcard
    By claireanddoug in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2005, 06:05 PM
  7. SUMIF, wildcard and cell ref
    By Dave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2005, 05:06 PM

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