Excel Magic Trick 1334

Power Query or Formula: Lookup Name Based on Max Value and moreā€¦


See how to use Power Query (Get & Transform) and a set of Formulas to create a report that Adds Total Units for Each Product, Finds Max Units Sold for Each Product, and Lists the Sales Rep Name Who Made Max Sale:
(00:16) Introduction
(00:57) SUMIFS to add with 1 condition
(01:59) MAXIFS to add with 1 condition (New Excel 2016 Office 365 Insider Edition)
(02:46) INDEX & MATCH Array Formula to retrieved name of Sales Rep with Max Unit Sale
(06:52) Power Query to Group By Product and Add Associated Units Sold
(08:30) Power Query to Group By Product with All Rows to Show All Records For Each Product. Action Returns a Table.
(10:18) Power Query Table.Max Function to get Record for Max Units Sold For Each Product.
(13:53) Summary


Download “Excel Magic Trick 1321-1340” Excel-Magic-Trick-1321-1340.zip – Downloaded 847 times – 98 MB

Be the first to comment

Leave a Reply