Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples

Download File: people.highlin...
In this video learn about:
1. (00:04) Download Files
2. (00:24) Aggregate vs. Array Calculations & Define Array
3. (02:55) Define Array Formulas
4. (05:49) Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income.
5. (06:43) Array Formula 1: Math Array Operation. Goal: Calculate Total Expense for each month without making calculations in many cells (we don’t need all the individual numbers). Main reason we use Array Formulas: To create compact solution to do all the intermediate steps in a single cell.
6. (10:00) How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell.
7. (12:06) Implicit Intersection.
8. (14:49) SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter.
9. (16:46) Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell.
10. (19:34) Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell.
11. (20:48) Formula 4: Use more than one argument in SUMPRODUCT. Goal: Calculate total Bank Deposit. Learn about SUMPRODUCT and discussion about size and dimensions of arrays in array calculations.
12. (25:06) Array Formula 5: Function Argument Array Operation in k argument. Goal: Add 3 biggest and 3 smallest. Use Array Constant to avoid Ctrl + Shift + Enter.
13. (28:32) Array Formula 6: Function Argument Array Operation in criteria1 argument. Goal: Add Sales for each Sales Team. Example of using an Array Formula to create a MUCH smaller formula than non-Array Formula alternatives.
14. No Example 7
15. (32:16) Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department.
16. (35:21) Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department.
17. (37:15) Array Formula 10: IF function array formula to filter out values for an aggregate function. Goal: Calculate Average Gross Profit for Each Sales Rep. Learn about how to use IF Function to filter out values that we don’t want for our aggregate calculation.
18. (38:12) List of Functions that can NEVER do Array Calculations.
19. (43:11) Array Formula 11: Built-in Array Function: TRANSPOSE. Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References. Example of Array Formula where we enter multiple values as an array into multiple cells simultaneously.
20. (45:42) Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category.
21. (50:31) Summary
Learn Array Formulas from Beginning to End.

[@shubhampawar8506]

Arrays formulas one of most difficult thing for me ...but you made it simple for me ....thanks a lot ❤️

[@wirldwind]

BEST Excel channel on KZbin by far!

[@excelisfun]

Glad you like it! Thanks for your Sub, and thanks for the comments and Thumbs Up : )

[@chantelhunsucker8908]

The attention to detail that you have put into every video workbook notes (next to the examples), plus the handouts for each video, truly reflects how much you care as a teacher. Thank you!!!

[@melissaramos3413]

Mike I’m following your videos since the first one and I have already reviewed them countless times so that I do not lose anything of your teachings. I work daily with excel and it's amazing how much I've evolved since I follow your amazing presentation. Despite all the positive comments I read about your videos, I can not avoid to stress how much your work, enthusiasm and dedication should have already improved the professional life of all who follow your presentations and who work daily with this fantastic tool which is Excel. For your work and dedication, thank you very much. João Ramos

[@excelisfun]

Thank you for your kind words, Melissa Ramos!!!! I can keep going all these years from the support from viewers like you : ) Thanks for the support : )

[@PieterSteens]

This was the most difficult subject I have encountered in your basic and advanced (up until now) excel series. I had to rewind a couple of times and really try to grasp what was happening. However I think I did a good job understanding what is happening under the hood of these array formulas. I will sleep on it for a night, then do the HW problems tomorrow. Another note: I really feel you can get creative with array formulas to solve problems that do not have a straightforward solution. This seems like the 'key' to more complex solutions to problems in Excel. Very interesting video as always and I thank you. On to video 6 and beyond!

[@Jojosmith342]

Thank you so much for teaching us and make our life easier. Thumbs up as always. Already subscribed. Also, thanks for sharing your valuable video. Save us a lot of time

[@nokibulalam2777]

May Allah bless you with all the good things for making these life-saving Excel videos.

[@excelisfun]

I am glad that the videos help, Nokibul!!!

[@TurboTeen29]

Still sticking to advance playlist and completing this playlist this month had so many problems to continue this excel series but still manage to continue thankyou very very much mike😀

[@excelisfun]

You are welcome! Excel, Power Bi and Data Analysis are what matter in the working world. People look at degrees, but once you get in, it is these skills that get you promoted and advanced!!!!

[@TurboTeen29]

@@excelisfun yes,you are right mike this is last year of my college and I want to be in field with some good knowledge of excel...thankyou for always relpyin it means a lot to me

[@nagizadeorxan]

This was another night that we were amazed by your great sharing, wish you all of the best!

[@excelisfun]

Glad you like it : )

[@skillhub5073]

best excel channel I ever found on youtube. it's excel treasure. Hats off.

[@excelisfun]

Glad you like it, Mitun!!!

[@johnborg6005]

Always good to go back. Never considered boring to the slightest. Thanks mike

[@excelisfun]

Ya, this is a good complete lesson in the Old Arrays : )

[@PhilShnider]

Thank you for sharing your knowledge Free of charge with the world, this nearly 500++ videos would benefit, not only today but for many years to come.

[@excelisfun]

+Amil Jayasinghe You are welcome!

[@sandribengkulu3290]

l

[@MySpreadsheetLab]

Incredible knowledge in this video! thanks Mike

[@excelisfun]

+Kevin Lehrbass , Thanks, Kevin. I think that this is the most complete and accurate Array Formula video I have ever done. Hopefully...

[@quangminh6851]

Awesome knowledge sharing, great effort as always. We can never thank you enough, Mr. Mike.

[@TheNabob57]

I truly appreciate your videos. I have been a great fan of Excel since the beginning and am fairly proficient with it; but I always learn something new from your series. Thank you so much!

[@excelisfun]

You are welcome so much! Thanks for your support with comments and thumbs up and sub : )

[@ELGORNATIANNA]

I am discovering your channel today. I am impressed. Thank you.

[@excelisfun]

Welcome, Anna! You are welcome for the material. There is a lot here - including full free classes, Excel workbooks to practice, notes about each topic and much more. If you have questions about how it all works, let me know. And, thanks for your support with comments, Thumbs Up and Sub : )

[@sherryizzie5309]

Function argument array operation in Sumifs criteria1 argument , how come I did not discover this earlier! Thanks, Mike! I picked up ONE MORE new trick from your Excel video! Hurrah!!

[@excelisfun]

Yes, I use this ALL the time. It works in COUNTIFS too : )

[@GoQatari]

Hi Mike, I really thank you as I have learnt loads from you and some other excel tutors' KZbin videos when I only knew simple if commands only. I have put together an array formula that is toooo long and can still be done better and simpler I believe. Almost on daily basis, I work with very long lists (1000+ lines) which have only one column. the cells in this single column are Alpha Numeric and are very unstructured and my job is to extract certain data which are not all properly tagged. I would like your guidance on who can help me improve the formula to improve the quality of the extraction. Appreciate your excellent videos and your kind guidance

[@Chamchijjige]

Watched. Refreshment tour. Thank you very much again.

[@pmsocho]

Thanks. That was huge video!

[@excelisfun]

+pmsocho You are welcome!

[@3eenab]

This is a very important lesson about arrays and should be reviewed many times. The pdf and the homework should be reviewed too.

[@excelisfun]

Glad it is a lot of help or you! Thanks for the support, 3eenab, with your comment, Thumbs Up and Sub : )

[@ginger3567]

Hi Mike, thanks for such a great video for array formulas. One concern always worries me and makes me feel unsecure when utilizing array formulas in my job. I have always been curious about the coincidence regarding how the "figure pairs" are perfectly understood and are fetched by Excel. For example, the when you are using the "if formula" to find all the all the Jack's sales by putting the two whole columns calculation into the "True argument" , the formula can understand your intention perfectly and will only subtract the "COGS" from the "Revenue" which are both locating on the same row! In addition, the results from the calculation are linked with the "True or False" judgement which is also done on the same row. It looks like we haven't told Excel to do so but it gets us the right answers still.

[@excelisfun]

I think Excel can faithfully put one of two items into a cell using the IF function, as you say "figure pairs". It is reliable : )

[@EricaDyson]

Totally agree. Fantabulous!! Thanks so much. Must admit that my head is spinning with arrays!

[@excelisfun]

You are welcome so much, Erica!!!

[@excelisfun]

Yes, in Microsoft 365 Excel the calculation engine has changed. Ctrl + Shift + Enter is no longer needed. Here is the updated video about array formulas: kzbin.info/www/bejne/bIvRgId9oNGJqbc

[@1tommyoneill]

Fantastic as always, I'm working through your videos step by step and learning so much. Brilliant stuff Mike.

[@excelisfun3903]

You are welcome, Tommy!!! That is wht I post! You can help out with a thumbs up and small comment on each vid : )

[@excelisfun]

+GoQatari I am not sure from what you wrote. In addition, I am quite busy these few weeks, so maybe you can post question to THE best Excel question site: mrexcel.com/forum

[@PedroCabraldaCamara]

Another amazing video!! I'm a lot less confused about array formulas. Tks a lot Mike

[@excelisfun]

+Pedro Cabral da Camara You are welcome a lot! Thanks for the Thumbs Up!

[@rockguitarist8907]

To anyone struggling with the Myers-Briggs example 8 around 36:00, I tried testing doing switching the criteria range with the criteria from what Mike did. This works fine the first go-round (when you're not using the ampersand 2nd go through example of the M-B), but Excel only seems to allow you to use the let's call it "& conjoined array" in your criteria, not in your criteria range. I don't understand why.

[@masterjack9505]

THank U teacher.

[@oapy123]

Has some behavior changed with newer versions of Excel? For the Array Formula 1 example referenced at 10m45s into the video - in cell B24 - I can enter the formula without pressing Ctrl+Shift+Enter and it still works - I don't get #VALUE error like this video shows.

[@jessipinkman7659]

The Excel God 🙏!

[@excelisfun]

Just trying to make the world a better place : ) :) One Excel Video at a time.

[@Chamchijjige]

Thank you very much again. I will be consitent and fisnih all your videos.

[@excelisfun]

You are very welcome! Keep on going : ) : ) : )

[@antoniorasco1928]

You are the best, man

[@excelisfun]

Glad to help, Antionio!!

[@elzadebelza]

Thank you soo much for every video - example etc that you have made!

[@excelisfun]

You are welcome!

[@Whyvardhann]

Amazing class, Mike!

[@excelisfun]

Glad you are enjoying it, Yashvardhan : ) : ) : )

[@techsilv5170]

Hi. Is it possible to do a table with a range of measure like 2 to 5 , give a price for this range and calculate a price when we put a value on a cell and calculate the price for different range of measures? Thanks

[@whatif35]

Hi Mike great video! can you please help? In the homework (sheet HW1 - cell I10) why can't we use the MAXIFS function to calculate Max Gross Profit?

[@guilhermep8414]

on 28:00 the curly brackets {1, 2, 3} are not working on my excel, I could find the biggest 1 or the second 2 or the third 3 separately. Do you think that maybe this has something to do with the outdated version of my excel? Great video btw !

[@sherryizzie5309]

Hi Mike, just a quick question, for "Score Myers-Briggs Test" example- Array formula 9 with join array operation, i switched criteria range 1 (scoring table) with joined "question" column and "answer" column and used "scoring" table as criteria 1, the countifs function does not work. However, countifs function works perfectly if the "join" helper column exists. I cannot get my head around it:(:(

[@excelisfun]

Can you show me the formula that you used and the minute mark in the video?

[@sherryizzie5309]

@@excelisfun : Hi Mike, for array formula 8 example, compared to your formula (recorded @ 35:08) I switched criteria_range1 with criteria 1, using SUMPRODUCT(COUNTIFS($C$120:$C$151,E120:E127)), I got the same answer as yours. However, when it comes to array formula 9 example, I applied the same logic, using SUMPRODUCT(COUNTIFS($A$120:$A$151&$B$120:$B$151,E120:E127)), this formula does not work with join operator, your formula (recorded @ 36:30) works magically:). Thank you for answering my silly question in advance.

[@excelisfun]

@@sherryizzie5309 Can you send me the workbook to excelisfun at gmail. maybe I can figure it out then...

[@houssamayoubi8715]

Amazing as usual, I can't thank you enough

[@excelisfun]

You are welcome! Your comments and thumbs ups are enough : )

[@Whyvardhann]

Great video, Mike!!!

[@excelisfun]

Glad you like it, Yashverdhan!!!! Thank you for your support : )

[@rabijaiswal5911]

Busn218-Video06.xlsm is missing from the Highline Excel 2016 Class Series. Please upload the excel file and thank you for the wonderful video series.

[@excelisfun]

I just checked, it is there. I hope you can get it now.

[@davebowman5392]

An excellent lesson, thank you.

[@excelisfun]

Glad it was EXCELlent for you, Dave!!!! Thank you so much for your support : )

[@ExcelxlNl]

Great video and suberb explanation (as always) Thank you

[@excelisfun]

+ExcelXL.nl You are welcome!!!

[@alexh.4842]

Bravo!

[@vinayakpotadar4093]

Super sir.

[@harrisneiditch6810]

THank you Mike for a great video! Quick question about the homework. For the last homework problem (HW 4), why is it not giving me the right answer if I just use the sumifs function and hit ctrl+shift+enter? Why is it only spitting out the right answer if I use the sumproduct? edit: Essentially. Why isn't the formula =SUMIFS($B$4:$B$45,$C$4:$C$45,$E8,$A$4:$A$45,F$4:F$7) working when I enter it in with ctrl+shift+enter

[@excelisfun]

Because the SUMIFS function is performing a function argument array operation and delivering multiple answers. If you enter SUMIFS into the cell, Excel says: "I can't show multiple items in a single cell!!!". In addition, your goal is to add all the numbers that SUMIFS delivers. You could use SUM, but that requires a special keystroke. So put SUMIFS in SUMPRODUCT (programmed to handle array operations without a special keystroke) and it will add all the numbers.

[@excelisfun]

Thanks for your support on each video that you watch : )

[@harrisneiditch6810]

Okay I think I understand. It's strange, I thought in those instances, even if I click CTRL+SHIFT+ENTER after entering the formula with only the sumifs in front of the formula...it would show the correct answer because the CTRL+SHIFT+ENTER calculates the formula as an array. Like, I thought that would serve as the answer for the cell "not being able to show multiple answers in a single cell". Thank you again! I think this is just something I will have to commit to memory essentially cause it is tricky.

[@lydericc8576]

Professor, if I do =MAX({1;2;3}), it works (I get 3). But if I write ={1;2;3} in cell A1, then do =MAX(A1) in another cell (with or without Ctrl + Shift + Enter), it doesn't work (I get 1). Am I doing something wrong?

[@excelisfun]

A single cell can't hold more than one value that other formulas can see. Excel is not programmed to do that.

[@billpipi1]

Thank you so much.

[@excelisfun]

You are welcome so much!!!

[@royalnass1029]

For Array Formula 6 I tried all 3 formulas and the only answers that I get is 0 or the sum of all the sales numbers of the entire table. These formulas are not giving me an answer for any particular sales tea. I don't know what I am doing wrong and I can surely use and appreciate some help with this question. If anyone knows what I am doing wrong then please reply to me with some assistance. Thank you and have a great day!

[@MrJamesHWard]

Amazing. Thank you.

[@Ryan-tt9qg]

awesome video series!

[@excelisfun]

Glad you like it!

[@yatinrana1263]

Hi, In example 8th i.e myers-briggs table when i am doing it with the help of join table i am doing it like this=SUMPRODUCT(COUNTIFS($C$120:$C$151,E120:E127)) and is giving me the desired result however when i am trying to join two columns if i am doing it like this: SUMPRODUCT(COUNTIFS($A$120:$A$151&$B$120:$B$151,$E$120:$E$127)) its giving me error why can anybody pls elaborate.

[@djavodjavo40]

My question refers to (31:41). How can I use 2 or more separate cells (not range) as Criteria1? For example If I want something like: =SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,(E70,E73)))

[@excelisfun]

Maybe: 1) Delete the criteria in cells E71 and E72 then just use same formula: =SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,E70:E73)) or 2) =SUMIFS($B$70:$B$111,$A$70:$A$111,E70)+SUMIFS($B$70:$B$111,$A$70:$A$111,E73)

[@excelisfun]

This one works also: =SUMPRODUCT($B$70:$B$111,--(($A$70:$A$111=E70)+($A$70:$A$111=E73)))

[@djavodjavo40]

Thank you for your response. I was aware of these solutions and the question was more theoretical. If it is possible to use 2 or more cell references, it is solution which can be applied even if these criteria cells are on different sides of the sheet (in that case probably you cannot use range), and formula will be less robust then with given Sumproduct and Sumifs solutions. It is possible to use constant arrays like: =SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,{"June","Poppi"})), and I was curious is there some way to put cell reference instead of constants to make it dynamic.

[@excelisfun]

Yes, array constants like {"June","Poppi"} would work

[@daledenton931]

one day I will become an Excel wizard thanks to excelisfun

[@excelisfun]

+Dale Denton Yes you will become an Excel wizard!

[@NoShadowOfDoubt1]

Thank you Mike

[@excelisfun]

You are welcome! Thanks for the support : )

[@Clifffffffffford]

Thanks

[@excelisfun]

You are welcome, clifford!

[@md.ishtiakazim9754]

done

[@SamerDebs]

Great Video thanks.

[@excelisfun]

+Samer Debs Glad you like it!

[@TheDiederikdehaan]

Hi Mr. ExcellsFun, Loved your video! Unfortunately the link to your pdf is missing. It sends you directly to the pdf of the next video. Because I really wanted to read your pdf-doc, could you please mend this error? Diederik

[@adv3nturetim]

people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm Link from the previous video with all the file and DF attachments

[@cmaman1]

Thank you sir

[@excelisfun]

+elomery m You are welcome!

[@nikolozkalichava1028]

Hey Mike, Thanks again, And how do I undo F9 formula evaluation without Ctrl+Z? One more thing, I had a problem with example 8. in E131 you have =COUNTIFS($C$120:$C$151

[@excelisfun]

Only Ctrl + Z un does it. I am sorry I do not understand your second question : (

[@Nikol0zi]

@@excelisfun sorry for my Gibberish. So. COUNTIFS is a 2 step formula 1 criteria_range# and 2 criteria# (if i only count 1 criteria) In this Workbook, Sheet-Array Formula, Example 8. When you started a COUNTIFS formula, your criteria_range1 was E120:E127 and criteria1 $C$120:$C$15. My first question, when we do a COUNTIFS formula, "criteria_range#" is "where we search" and "criteria#" is "what we search", right? The task was to search for cells from E120:E127 in a table C120:C151, But you did the opposite. You search for C120:C151 in E120:E127. Your formula in E131 is =SUMPRODUCT(COUNTIFS(E120:E127,$C$120:$C$151)) while my formula is =SUMPRODUCT(COUNTIFS(E120:E127,$C$120:$C$151)). The difference is in COUNTIFS, criteria_range and criteria are swapped in my version, BUT BOTH, YOUR FORMULA AND MY FORMULA, WORK :O First of all, WHY does YOUR formula work? And second question: Example 9, When i use my version of formula above, but with "&", it will not let me press Enter.... I don't get it.... Why?

[@planxlsm]

0:50 16:56 23:40 26:31

[@SantoshJaiswal-gv5tu]

hi sir help me out with error in VBA editor i am sending u error screen shot

[@user-hb2rt7ek8x]

why there is no sound?

[@chitti8454]

pls improve video quality..

[@Chamchijjige]

Watched. Refreshment tour. Thank you very much again.