Eric P. Butts

THE Career Site for Accountants and Consultants.

  • Home
  • About
  • Useful Resources
  • Articles
  • Work With Me
    • Breakthrough Resume Writing
    • How to Get Smart in Excel…Fast!
    • Corporate Ladder University (Book)
  • Privacy Policy
  • Terms of Use
  • Disclaimer
  • Login
You are here: Home / Excel / How to Use Excel Wildcard With SumIFs Formula

May 27, 2016 by Eric Butts 4 Comments

How to Use Excel Wildcard With SumIFs Formula

Excel Wildcard and Sumifs work wonders together.
Click to see the wildcard functionality in action.

Do you know about the Excel wildcard functionality?

You know, the one where you put an “*” before and/or after something you’re searching for and Excel executes a search based on what you input. For example, imagine you have a spreadsheet with a lot of data. Maybe it’s an export of transaction activity for the month. You have hundreds of account numbers ranging from 1xxx through 9xxx. You want to spend a little extra time on the 8xxx accounts because that’s where the bulk of activity gets posted. You can do one of two things:

Option 1: Add a filter, go to the filter drop down and unselect all accounts, then scroll down to the accounts you want to look at and manually check them.

Option 2: Add a filter, go to the search in the filter drop down and type in “8*”. Not only is option 2 faster (Under option 1, you could be scrolling for days through a bunch of accounts that show up because of one row of data in your file.), but it’s also more reliable. Using a manual approach, you could easily miss a box that you meant to check and skew your analysis.

Pretty sweet, right? And it gets better.

You can also use Excel wildcard functionality in formulas, and I’m going to show you how with another example. Say you have this small table of data:

Use Excel wildcard and sumifs to save yourself some time

Note: “X” represents contractor and “E” represents employee in the grade column and the number represents level with the respective contractor or employee category. Now, let’s say you want to use formulas to summarize a few different pieces of information:

1. Salary paid to employees and contractors, respectively

2. Salary paid to employees and contractors with the last name “Brown”

3. Salary paid to employees whose names contain “Brown” in them.

How would you attack this problem? Looking at number 1 above, you might be tempted to just add a column to flag each employee as a flag and then use a sumif formula, but that strategy quickly becomes cumbersome and impractical as the number of extra columns needed in your dataset increases.

This is where the Excel wildcard function comes in handy. Here’s how to take down each of the above:

1. For salary paid to contractors, type =SUMIFS(E4:E11,D4:D11,”*X*”). For employees, do the same thing but replace the X with an E in the formula.

2. For salary paid to contractors with the last name Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown”). Again, for employees, do the same thing but replace the X with an E in the formula.

3. For salary paid to contracts with the last name containing Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown*”).

You notice the slight difference between 2 and 3? Trust me when I tell you adding this functionality to your repertoire will save you a lot of time. There is one other formula I used to put this example together.

Click the button below for the video or join my free content library and then navigate here.      

        Click Here to See Excel Wildcard in Action

Filed Under: Excel, Management Consulting Tagged With: excel, excel formulas, spreadsheet, sumifs

About Eric Butts

I’m a management consultant, MBA and CPA who has a passion for helping others in their career pursuits. Grab my FREE cheatsheet on 12 simple habits of highly successful consultants.

Comments

  1. David says

    May 28, 2016 at 7:27 am

    Solid tip Eric – thank you! I’m guilty of the extra columns to extract the info I want to use. This tip will save me time in the near future. Keep them coming.

    Log in to Reply
    • Eric Butts says

      May 28, 2016 at 8:08 am

      Gald I could help! I know your excel game is already strong.

      Log in to Reply
  2. D.A. Trappert says

    June 3, 2016 at 6:26 am

    This is a good tip, but it is also worth exploring the use of pivot tables. I used to create a lot of complex spreadsheets with all sorts of SUMIFs, but once I discovered pivot tables I realized how easy it is to slice and dice data that way. It’s a much more elegant and less error-prone way to analyze data without the risk of messing up one of your formulas.

    Log in to Reply
    • Eric Butts says

      June 3, 2016 at 6:33 am

      Pivot tables are great (one of the best featured in Excel I would say) but don’t fit the requirements of every task. I’m working on an example of this for the course I’m building out. It’s basically a raw table that needs to be summarized but using a pivot makes the info a little more challenging because of refreshes and the formatting can be a little awkward for some if you’re not used to working with them. My example is more with countifs though, and the wildcard options works there too.

      If you’re on my email list, you will hear more about that soon.

      Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

Featured Posts

5 Ways to Avoid The Manager Power Trip

Case Study: When Should You Protect Your People?

Sometimes You Have to Do First and Ask Questions Later

Why All Your Years of Experience Mean Nothing

How to Get Into Business School – Part 4 (Recommendations)

Popular Posts

  • 5 Reasons You Should Be Proud to Ask Dumb Questions at Work
  • What Does It Mean to Have a Sense of Urgency
  • Always Stay Close to the Gatekeepers
  • Why Are There Still So Few Black CPAs?
  • What's the Difference Between FP&A and Accounting?
  • 3 Reasons Average Students Become Good Accountants
  • One Thing You Should Never Do on Linkedin
  • How to Handle Double Standards in the Workplace
  • How to Pass the CPA Exam Without Breaking the Bank
  • Masters in Accounting Isn't The Sweet Treat Some Students Think

Recent Posts

  • How To Customize A LinkedIn Invite From Your Phone
  • Weird non-flex but ok
  • The little video game that could is putting on a masterclass in marketing yourself by picking a fight with two juggernauts
  • Today’s message is brought to you by middle of the night confusion
  • This. Is. Sparta!
  • Privacy Policy
  • Terms of Use
  • Disclaimer

© 2012–2021 Eric P. Butts