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

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:
1. (00:13) Introduction
2. (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.
3. (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.
4. (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.
5. (12:50) Method 4: Look at Finished Array Formula that does not have to alter the original data set.
6. (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.
7. (15:20) Test Methods to see if the solutions update when data changes or data is added.
8. (16:35) Method 6: Old Accounting Trick: SUMIFS and Divide by 2.
9. (17:46) Summary.

[AriahSV]

Do you know how long I have been waiting to know how to do this? I just stumbled across this one whilst studying every night using your other videos and boom, there it was!! right at the moment when I really needed it as I need to complete something at work for which these tricks are absolutely perfect. Your videos are incredibly well put together, highly educational and easy to follow, thank you and keep up the great work!

[BillSzysz]

As you wish Mike ;-)
Insane but not array formula (it means... hidden array) ;-)
=VLOOKUP("Total", INDEX($B$57:$B$71, MATCH(F57, $A$57:$A$71, 0)): $C$71, 2, 0)
Thanks for video!!!

[DougHExcel]

Thanks for the video...lot of great examples!

[aliasgar]

Very Nice Video. thank for sharing the great knowledge!!!

[muhammadfathi]

Thanks for awesome video!!! always helpful....

[jitendrakumarsoni]

Sir ji,
How to calculate TD
TD full form till date
Plz help me

[thx]

yours videos are very good or even excellents for to learn the language english due to your great pronunciation anymore that is very good namely : That it all seems fantastic and seems funny Kinda like a dream !!
excelent english persons not have this pronunciation, their pronunciation ain't workable..in other words : their pronunciation is very bad and a poberty of lenguage
best regards !!

[chriswebb]

Great video as always. I think with an update Excel is giving me a Spill error with the "istext" function. Just and FYI, thanks for all you do!

[jeffreystockdale]

use sumifs by name then divide by two.

[thx]

please do it a pay roll in tables dynamic and in macros.
I need to do a workforce !!
thanks so much .
best regards!

[TheSaks]

Hi, I'm have office/excel 2013, how do I show the gray formula dialog box below the cell with the actual formula, Is there a checkbox somewhere under file settings or something? Great how to videos, best I've seen so far.

[Barhomopolis]

We really need a clone of you and your channel for MS Access. Do you know one, Mike?

[anil]

my data set every 5 row is total, i want fill and skip every 5 row

[Excel-end]

Hi sir,
=VLOOKUP($G$20, OFFSET($A$20, MATCH(F21, $A$21:$A$35, 0), 1, ROWS($B$21:$B$35), 2), 2, FALSE)
this is my solution.., ,