Excel Magic Trick 1329: Clean Data Then Add w 2 Criteria: SUMIFS or Power Query (Get & Transform)?

Download Excel Files:

See how to Clean a data set and then add with two criteria using SUMIFS function and Wildcards with the criteria or Power Query and Group By feature:
1. (00:14) Introduction
2. (01:44) SUMIFS function and Wildcards with the criteria
3. (05:25) Power Query and Group By feature
4. (12:03) Summary

[MySpreadsheetLab]

for long term solution your power query method is great especially once excel 2016 is used by vast majority of users. Sumifs with wildcard is also great solution but we'd have to be watching for new criteria pairs. Power Query solution is much easier than possible vba solution.

[jasonm]

Good stuff.
Wildcards are wildly powerful.
I knew I had used Group by before. Thanks for reminding me.

[lazalazarevic]

great use of wildcards in formulas
thanx for the tip

[imranhussain]

It's amazing, as always your videos are a great help for those who really wanna be excel expert. :)

[maneshzaveri]

Great video - the use of wildcard is really good to know and I am starting to appreciate the power of power query also. Good start to the day in Singapore :-)

[mohamedchakroun]

Thank you mike you are doing amazing things with excel :-)

[johnprice]

Love your videos. Quick question: What if I have 10 columns of data in a table and I want to use group by on two fields like you have done in your video but I also want to keep the additional column data after using Group By? Using Group By removes all other column data except the users selected data and whatever column they used to aggregate the data with.

[imranhussain]

Adaptation of a new version(of excel) in companies usually considered as difficult as appointing the whole new staff. :)

[FocusProj]

Amazing illustration of efficient working. But is it true that you can do all that in VBA as well?

[waheedqureshi]

hello my Friend Please one video on the formulas list, how to create a list of formulas that we can change result with it. like sum, average, max, min, count, with list can you help me on this matter.

[waheedqureshi]

hey! I'm Waheed Qureshi from Pakistan,

I have a Problem in excel,

I saw that list formulas function in the template, that template have a list of formulas that we can change list And we can and result change automatically in the one cell,
I don't know how it work, Please one video on the Formulas list than we can change our result list changing the list like sum, average, Max, Min, Count Number, with list.

Please One video on the formulas list with this We can change result any time without putting the formula again and again in the Excel.


Waheed Qureshi