Ctrl + Shift + Enter: Excel Array Formulas #05: Function Argument Array Operations

EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!

This video covers:
1. (00:20 min) Array Formula that makes "function argument" array operation: function argument expects a single item, you give it more than one item.
2. (00:31 min) Formula goal: Lookup and then add all costs associated with various products listed in column ("Lookup Adding")
(01:10 min) Helper Column with VLOOKUP
3. (02:06 min) Defined Names. Create Names From Selection, including keyboard shortcuts.
4. (04:27 min) Enter multiple values into a function argument that expects a single value to create an array.
5. (05:56 min) Some function arguments that are programmed to perform array operations without requiring Ctrl + Shift + Enter (SUMPRODUCT example).
6. (07:35 min) Why not use SUM function instead of SUMPRODUCT?
7. (08:07 min) Calculating notes about Array Formula =SUMPRODUCT(SUMIFS(Cost,Product,ItemSold)).
THIS VIDEO SERIES AT YOUTUBE IS THE SAME AS THE DVD FROM EXCELISFUN. THESE VIDEOS ARE BEING GIVEN AWAY FOR FREE AT YOUTUBE. SUPPORT THE CAUSE BY GOING TO AMAZON AND BUYING THE BOOK.
EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!
Ctrl+Shift+Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formulas for Data Analysis and Calculating Problems
Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots.

[excelisfun]

Ctrl + Shift + Enter: Excel Array Formulas #05: Function Argument Array Operations
This video is the same as the video on the Ctrl + Shift + Enter: Mastering Excel Array Formula DVD, but it is being given away for free here at YouTube.
To Support the cause, go to Amazon and buy the book or DVD. Book and DVD title: Ctrl + Shift + Enter: Mastering Excel Array Formulas.
There are 25 videos in this series (0 to 24).
Remember: Excel Array Formulas work the SAME in any version of Excel!!!

[rodrigocustodio]

Thank you, Mike,
You are the best teacher I ever had, such a talent to explain things.

[ginger]

Such a good way for me to sharpen both skills at the same time! Lol, I just learned how to use the word "trump" today! Besides, I also got some idea about the "if-aggregate" problem which I encounter last time. A big thank to you! Mike!

[NeoJack-one]

We are grateful for your dedication, thanks again mike !!!😊👍

[Needforexcel]

Well, what would have the Excel World done without you?? Amazing video! :) Cheers.. 

[basavarajkabadad]

Awesome am going through all videos .... Good job Mike 👍 much appreciated for your hardwork to prepare such a master data ... 6 year ago videos can you believe it which is helping me lot.... Am new and learning excel array formula

[ExploringExcelAmey]

Thanks for the cool video
The very interesting part is Array formulas work in any version of excel

[aknoimak]

It is beautiful.
Thank you  ExcellsFun.

[zoeclearwater]

Thank you for this post very helpful. I had to update a free template from ms office and they used a sum function for total hrs worked a week with just shift names on one work sheet and times on next.

[prasitkeng]

I wonder why {=SUM( VLOOKUP(C8:C12, $B$16:$C18, 2, 0) ) does not work for this case.
I have tried that and it's like VLOOKUP function could not return an array.

[abdulllahmadil]

i need help... how to put this :

IF SALARY >= 5565.61 THEN SALARY X 12.5%
IF SALARY >= 3566.05 OR SALARY <=5565.60 THEN SALARY X 15%
IF SALARY >=1783.84 OR SALARY <=3566.04 THEN SALARY X 17.5%
IF SALARAY <=1783.83 THEN SALARY X 20%

in excel cell.. please help me...

[emre]

hello could you please help me to solve my issue ? in my report, I used sumproduct formula and using sum formula its calculating the values on the table (around 100 cells) but its taking to much time
I tried everything that I know before but its not working. i have share the report with my manager I can send you the book via email could you please help me