Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Sumifs statement with non-wild asterisk as criteria

    I'm doing a report summarizing data from a detail tab by share code via sumifs formulas. My company apparently thought it was a good idea to have some share codes with asterisks as the first character and this is making it particularly difficult to summarize using sumifs. For example, on my details tab I have both a share type "FC" and a share type "*FC". If I set the latter as the criteria, it pulls the total balance for both types. Is there a way to lock down the asterisk in the sumifs query so that it is not recognized as a wildcard?

    Scrtchmstj

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If the asterisk is always at the begining you could try this
    =SUMIFS(B2:B3,A2:A3,"?FC")

    This will sum only '*FC' anf ignore 'FC'

    However, if you have any other share codes that start something FC then it will sum thoes also.

    ??

    MTB

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Whilst it might be a bit horrible, you could use SUBSTITUTE()...


    Add a new column (C) with the following formula
    Code:
    =SUBSTITUTE(A2, "*", "!!")
    Then your final calc should be changed to:
    Code:
    =SUMIFS(B2:B3,C2:C3,SUBSTITUTE("*FC","*","!!"))
    Basically we are swapping the stars out for a double-exclamation mark (or something equally unlikely to appear, e.g. tilde, carat, etc) and searching for that value.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Thanks MTB, that works. I actually ended up having to go a little different route so as to allow the referencing of a cell to the left with "*FC" in it. I ended up going this route:

    =SUMIFS(Detail_TabI:I,Detail_TabJ:J,I113)-SUMIFS(Detail_TabI:I,Detail_TabJ:J,RIGHT(I113,2))

    Basically taking the total of all ending in "FC" and subtracting those that are only "FC" leaving behind those that are "*FC"

    Scrtchmstj

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    George, thanks for your idea too.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    You can escape wildcards in Excel by using a tilde ~ so your criteria for "*FC" would become "~*FC"
    Section 4.3 on my blog post covers wildcards in SUMIF (which are exactly the same in SUMIFS):

    https://colinlegg.wordpress.com/2012/04/06/sumif-formulas/

Posting Permissions

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