Advanced Excel Formulas & Functions | SUMIF/IFS; COUNTIFS; AVERAGEIFS; SUMPRODUCT & more | Lesson 7
Grab the lesson file here 👉🏽 https://beyond-basics.online/ms-excel... Welcome to Lesson 7 of the Ultimate Advanced Excel Free Course available exclusively on YouTube! This lesson provides a comprehensive guide of advanced excel functions like SUMPRODUCT, COUNTIF & COUNTIFS; SUMIF & SUMIFS; AVERAGEIF & AVERAGEIFS and MAXIFS & MINIFS functions. In this video, we cover: 1. Data Analysis Functions including SUMPRODUCT and AGGREGATE functions 2. Basic Array Multiplication and Summation using SUMPRODUCT 3. Conditional Summing using SUMPRODUCT 4. Calculate Weighted Average of Exam Scores using SUMPRODUCT and SUM functions 5. Conditional Counting using SUMPRODUCT 6. Using LARGE and SMALL functions to identify top and bottom performers 7. Conditional Aggregation Functions 8. Count Blank/ Non-Blank Cells using COUNTIF function 9. Count Votes using COUNTIF function 10. Count Scores between Certain Range using COUNTIFS function 11. Using wildcards with SUMIF and SUMIFS functions 12. Using wildcards with AVERAGEIF & AVERAGEIFS functions 13. Calculate Average Temperature in Celcius during Working Hours using AVERAGEIFS function 14. Calculate Total Sales based on the multiple criteria using SUMIFS function 15. Find Maximum/Minimum Values using MAXIFS and MINIFS functions Download the Ultimate Excel Prep Pack here 👉🏽 https://beyond-basics.online/product/... Purpose of the Video The purpose of this video tutorial is to empower viewers with a robust understanding of various advanced Excel functions used for data analysis, calculation, and aggregation. By mastering these functions, viewers will be able to perform complex data manipulations, analyze datasets more efficiently, and extract meaningful insights from their data. Key Takeaways • Understand how to use SUMPRODUCT for both array multiplication and summation. • Learn to utilize AGGREGATE for performing operations like summing, counting, and averaging while ignoring errors or hidden rows. • Master the basics of multiplying arrays and summing the results with SUMPRODUCT for complex calculations. • Learn to perform conditional summing, which allows summing only specific values in an array based on given criteria. • Calculate weighted averages efficiently by combining SUMPRODUCT and SUM functions to handle weighted datasets. • Use SUMPRODUCT to count occurrences that meet specified conditions, enhancing data analysis capabilities. • Identify and rank top and bottom performers in a dataset using the LARGE and SMALL functions. • Apply conditional aggregation functions to perform operations based on specified criteria. • Use COUNTIF to count the number of blank or non-blank cells in a dataset, which is useful for data validation and cleaning. • Learn to count votes or categorical data efficiently using the COUNTIF function. • Utilize COUNTIFS to count the number of scores falling within specified ranges, supporting multi-criteria counting. • Master the use of wildcards in SUMIF and SUMIFS functions to sum data that matches partial text criteria. • Apply wildcards in AVERAGEIF and AVERAGEIFS functions to calculate averages based on partial text matches. • Calculate average temperatures for specific time frames using AVERAGEIFS, showcasing time-based data analysis. • Perform complex summations based on multiple criteria to calculate total sales accurately using SUMIFS. • Use MAXIFS and MINIFS functions to find maximum and minimum values that meet specified criteria, useful for detailed data analysis. By the end of this tutorial, viewers will have a solid understanding of how to leverage these powerful Excel functions to perform advanced data analysis and manipulations, enabling them to work more efficiently and effectively with their data. Timestamps: 00:00 Intro 00:42 SUMPRODUCT function – 4 examples 11:20 AGGREGATE function 15:36 LARGE & SMALL functions 20:35 COUNTIF & COUNTIFS functions 38:17 SUMIF & SUMIFS functions 44:06 AVERAGEIF & AVERAGEIFS functions 53:24 Complex Example # 1: AVERAGEIFS with multiple criteria 01:06:52 Complex Example # 2: SUMIFS with multiple criteria 01:11:56 MAXIFS & MINIFS functions If you found this tutorial helpful, please like the video and subscribe to our channel for more comprehensive Excel tutorials and productivity tips. Don’t forget to leave a comment below with your questions and share your experiences with using Excel. Follow us on social media for updates and join our community to continue learning and improving your Excel skills! #ExcelTutorial #DataAnalysis #ExcelFunctions #SUMPRODUCT #AGGREGATEFunction #ExcelTips #ArrayFormulas #ConditionalSumming #WeightedAverage #ConditionalCounting #ExcelTopBottom #ExcelAggregation #COUNTIF #COUNTIFS #SUMIF #SUMIFS #AVERAGEIF #AVERAGEIFS #MAXIFS #MINIFS #ExcelForBusiness #SpreadsheetSkills #LearnExcel #ExcelMastery #ExcelFormulas #DataScience #DataSkills #OfficeSkills
