How to Use SUMIFS with Partial Match and Wildcards in Excel

This video provides a comprehensive tutorial on how to use the SUMIFS function in Excel for summing values based on partial text matches. The SUMIFS function is typically used for summing values with exact criteria matches, but this tutorial explores its application for partial matches using wildcard characters.

The tutorial explains the use of wildcard characters like the asterisk (*) and the question mark (?) to handle various partial match scenarios:

Summing Quantities with Any Partial Match: The presenter shows how to sum quantities where the company ID contains 'AT' anywhere in the text. This is achieved by placing asterisks before and after 'AT' in the criteria section of the SUMIFS formula.

Restricting to Specific Characters: The tutorial progresses to more specific cases, such as summing quantities where '10' is at the start, end, or a specific position (like the fifth and sixth characters) in the company ID. This part of the video illustrates the use of question marks to represent fixed placeholders for characters.

Handling Complex Criteria: The video also addresses more complex scenarios where multiple criteria are involved, like summing quantities based on both the first two and last two characters of the company ID. The presenter skillfully combines the LEFT and RIGHT functions with SUMIFS to achieve this.

Adding Additional Criteria: Lastly, the tutorial demonstrates the flexibility of SUMIFS by showing how to easily add more criteria to the formula. This section is particularly useful for users who need to adapt their formulas to evolving data analysis requirements.

Learn how to SUM values in Excel based on a partial text match. This is great for cases when you need to sum a column based on "criteria contains" a specific value or text.

The asterisk is a placeholder for zero or more characters. The ? mark is a fixed placeholder in terms of the numbers of characters. If you use the question mark once, this means only one character can be variable. If you use it two times, then two characters can be "anything". You can combine the question mark together with the asterisk sign as well. I show different examples on how you can do that in this video.

I also show you how you can sum based on two or more conditions. Here the SUMIFS formula comes in really handy.

LINKS to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[marcvslicinivscrassvs]

I've been following your videos religiously and even taken several of your Udemy courses (right now in the dashboard course).

You're an excellent teacher! This newly minted MOS Excel expert salutes you.

[Cmdr_Amethyst]

Leila you really do deliver master class-tier content for Excel, and I find myself *uniformly* inspired to do better through watching your videos. You treat the audience with respect, and it is so very much appreciated. Thank you for being an amazing content creator =]

[ravishekharsingh]

Explanation of how to use wildcards with ifs function is really amazing. It helped to deal with large data set with multiple criteria. This simple explanatory video really helped me a lot. Thank you for posting this video

[ericsn]

This is a great solution to a problem I had... I can't believe a simple wildcard is the answer.
Thank you Leila.

[samantablur]

Wonderful, perfectly explained, no time waisted, and so so helpful, tanks very much

[wayneedmondson]

Hi Leila.. great tip and so logical. Before watching, I first tried something like this: =SUMPRODUCT(ISNUMBER((SEARCH(D1, A1:A3)))*B1:B3) with my criterion range in A1:A3, my sum range in B1:B3 and my criteria in D1. It works, but maybe is cumbersome to understand than SUMIFS() with its structured arguments. I never thought of using the wildcard characters to modify the criteria within SUMIFS(). Of course, that makes sense, now that you provided the example and opens new possibilities, as you demonstrated with LEFT and RIGHT. Excellent! Thanks and Thumbs up!

[atulkabra]

Thanks Leila. All your videos are really useful and informative. I think your skills of explaining things precisely makes all your videos more relevant and valuable.

[TomBsChannel]

I think from a practical perspective, this has to be one of your best videos. Fantastic. Thank you very much.

[tanveerhussain]

your teaching style is so kick-ass....love you

[henryg]

Just come across this video. Brilliant, Leila!! Never realised it was possible to do this. Will solve so many problems.

[tomnicolle]

You are so awesome Leila! You make everything so easy and simple with your wonderful teaching. Thanks so much as always! God bless you for your gift!

[hasnainak]

No doubt, This is the best video on Advance excel sumifs function.

[edot]

Guess you helped in building Excel. You are just too much! I wouldn't be surprised you would be introducing some new functions to to the Excel developers soon. You are awesome!

[ulrichboussouka]

Thanks Leila you are always the best excel lecturer ever..

[oxanamelnic]

Thank you very much for detailed explanation.... really helped me and saved my time on figuring out for certain caracters 😘😘😘😘

[thairtalib]

It was simple, clear and rich, god please you .

[lofhof]

I had this problem with countifs in the past. Now it works. Thank you.

[zahirpaki]

Excellent. I always get solutions from your channel regarding Excel.

[martijnfransen]

Excellent Leila even though I'm quite advanced in excel there is always something to learn and this is the place to do it..😊

[richknoll]

I thoroughly enjoy all of your videos. They are very clear and concise and have helped me solve a lot of problems and streamline a lot of processes at work. Thank you very much for the time and effort you put into this. It is very much appreciated.