How to Use SUMIFS with Partial Match and Wildcards in Excel
103.7 هزار بار بازدید -
6 سال پیش
-
Join 400,000+ professionals in our
Join 400,000+ professionals in our courses here 👉 https://link.xelplus.com/yt-d-all-cou...
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.
⬇️ DOWNLOAD the workbook here: https://pages.xelplus.com/sum-partial...
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:
Absolute Relative referencing: Excel Cell Reference: Absolute, Relat...
Excel SUMIFS Basics: How to Use SUMIFS, COUNTIFS and AVERA...
SUMIFS between Dates: Excel SUMIFS Date Range Formula | Sum...
★ My Online Excel Courses ► https://www.xelplus.com/courses/
➡️ Join this channel to get access to perks: @leilagharani
👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: https://www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: Instagram: lgharani
LinkedIn: LinkedIn: xelplus
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
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.
⬇️ DOWNLOAD the workbook here: https://pages.xelplus.com/sum-partial...
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:
Absolute Relative referencing: Excel Cell Reference: Absolute, Relat...
Excel SUMIFS Basics: How to Use SUMIFS, COUNTIFS and AVERA...
SUMIFS between Dates: Excel SUMIFS Date Range Formula | Sum...
★ My Online Excel Courses ► https://www.xelplus.com/courses/
➡️ Join this channel to get access to perks: @leilagharani
👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: https://www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: Instagram: lgharani
LinkedIn: LinkedIn: xelplus
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
6 سال پیش
در تاریخ 1397/09/08 منتشر شده
است.
103,780
بـار بازدید شده