MSPTDA 27: SUMPRODUCT & SUMIFS Worksheet Array Formula to Add Units in Many To Many Relationships

Download files for videos: http://bit.ly/2KhPz9Z
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video: http://bit.ly/2KicfXm

In this video learn about how add total units from a Fact Table with Book ID. See two formulas: Total Units for each Book ID, but then also Total Units for Each Author when each book may have multiple Authors, in a Many-To-Many Relationship. Learn about the Plus Math Operator for OR Logical Test. See the IF Function to lookup correct Book IDs based on OR Logical Test.
Topics:
1. (00:15) Introduction
2. (01:12) Many-To-Many Relationships. What is a Many To Many Relationship?
3. (02:34) Worksheet Formulas and caveats
4. (03:22) SUMIFS, INDEX and MATCH Worksheet Functions to create a Total Units for Book ID Report.
5. (06:01) SUMIFS, INDEX, MATCH, IF and SUMIFS Worksheet Functions in an OR Logical Test Calculation to help simulate a Many-To-Many Relationship with the goal of creating to create a Total Units for each Author Report.
6. (08:05) OR Logical Test
7. (12:52) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Further Help

I offer limited consulting services to potentially assist you with data challenges, whether it's designing a complex Excel formula, writing a macro or building a whole new process for data capture, modeling and analysis.  Contact me if you have a need.