Скачать или смотреть Excel At The ModelOff Championships -- Sum / Average Multiple Criteria: Episode 1618

Microsoft Excel Tutorial: Multiple Criteria in SUM and AVERAGE functions.

Bill 'MrExcel' Jelen: [Almost] Live From New York City to judge the ModelOff Worldwide Excel Championship!
Welcome back to the MrExcel netcast! I'm Bill Jelen and we are still in New York City at the ModelOff World Finals. In this episode, we have Martijin back with us to demonstrate a fantastic trick for using multiple criteria in the SUM and AVERAGE functions. Watch how quickly he is able to navigate through the keyboard to achieve his desired result.

Martijin starts off by explaining that when using more than one criteria, the traditional SUMIF or MEDIANIF formulas won't work. However, there is a solution - the SUMPRODUCT function. This function looks at the criteria as a factor and returns either a 1 or 0. The syntax for this function is crucial, so pay close attention. You will need to use two brackets, select your date range, and specify your criteria. For example, if you want to find the sum of amounts that are larger than a certain date, you would use the formula =SUMPRODUCT((Dates＞First Date)*Amounts).

But what if you want to find the average instead of the sum? Martijin has a solution for that too. He suggests adding another column with a count formula to get the average. And here's a hot tip - to quickly move a formula to the right, simply use the shortcut Ctrl+R. Martijin also explains that he could have used a relative reference, but in this case, he chose to just move down the formula.

Now, you may have noticed that Martijin used a multiplication sign instead of commas in his formula. This is because using the multiplication sign converts the criteria from True/False to actual 0's and 1's, which is what the SUMPRODUCT function needs. This is the preferred method according to Bill, and we have to agree with him. So, if you want to impress your colleagues with your advanced Excel skills, make sure to use the multiplication sign in your SUMPRODUCT formulas.

If you're interested in participating in the ModelOff World Finals, you can enter next year's competition in 2013. The first two rounds are done at your desk, and if you do well, you'll get to travel to New York City for the finals. It's an incredible opportunity to showcase your Excel skills and compete against the best in the world. So, make sure to check it out and who knows, maybe we'll see you at the next ModelOff World Finals. Thanks for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) SUM/Average Multiple Criteria Trick
(00:18) Using SUMPRODUCT for Multiple Criteria
(01:16) Getting the Average with SUMPRODUCT
(02:03) Hot Tip: Using Ctrl+R to Move Formulas
(02:26) Discussion on Using Mouse vs Keyboard
(03:01) The Right Way to Use SUMPRODUCT
(03:30) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Adding a column with counts
Calculating sum with some product
Criteria formulas
Multiplying criteria with some product
Some products
SUM/Average Multiple Criteria
Syntax for some product
The importance of using multiplication instead of commas in some product formula
Using brackets and date ranges with some product
Using Ctrl+R to move formulas
Using multiple criteria with some product
Using the mouse to move arrays in formulas


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

ith 16 Excel Rock Stars, from around the Globe, in NYC -- and Bill having access to them all -- why not do some Podcasting OnSite!

Bill is with Matijn Reekers for our last 'Almost Live from The ModelOff championships' series. Today, in Episode #1618, Bill and Matijn are looking at Summing and Averaging Multiple Criteria in Excel.

ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20...

"The Learn Excel from MrExcel Podcast Series"

Visit us: MrExcel.com for all of your Microsoft Excel Needs!