Excel Magic Trick 1372

Extract Only Total Row For Each Name: Formula or Power Query (6 Examples)

DESCRIPTION

See how to Get the Total for each employee when the Employee Name column has many empty cells and the “TOTAL” is in a row without an employee name. See five methods:
(00:13) Introduction
(00:37) Method 1: INDEX and MATCH for a consistent pattern where “TOTAL” is always two rows below Employee Name. This solution comes from: Dean Bailiff from YouTube. This solution does not have to alter the original data set.
(02:58) Method 2: Use Go To Special Blanks to replace empty cells with Employee Name. Then use SUMIFS function. This solution alters original data set.
(06:20) Method 3: Create Helper Column with “Lookup Last Text” Array Formula that uses LOOKUP Function. Then use VLOOKUP Function with Helper Column. This solution alters original data set.
(12:50) Method 4: Look at Finished Array Formula that does not have to alter the original data set.
(13:06) Method 5: Use Power Query to create finished Report. This solution does not have to alter the original data set, but does require that data in in an Excel Table using the Excel Table feature.
(15:20) Test Methods to see if the solutions update when data changes or data is added.
(16:35) Method 6: Old Accounting Trick: SUMIFS and Divide by 2.
(17:46) Summary.

WORKBOOKS

Download “Excel Magic Trick 1361-1380” Excel-Magic-Trick-1361-1380.zip – Downloaded 425 times – 14 MB




Be the first to comment

Leave a Reply