Excel Magic Trick 1334

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

DESCRIPTION

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

WORKBOOKS

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




Be the first to comment

Leave a Reply