Clipboard trick. Key, especially when building a large LET() formula. Everything right there, like a restaurant menu. Thank you!

@excelisfun

11 ай бұрын

Yes!!!!! Restaurant menu simile is perfect : ) : ) : )

OK Mike, this is a game changer for me: I didn’t know that XLOOKUP can break the spell that’s on the “IFS”-functions, you know: the range vs. array conundrum. This has serious implications for writing LAMBDAs. Thank you for making this video. We needed this. (Others may have known this, but I didn’t)

@excelisfun

11 ай бұрын

Yes, the magic of ranges from INDEX and XLOOKUP : ) There are always hidden secrets that Microsoft and us didn't know would have an application that we didn't know lol

@josh_excel

11 ай бұрын

@@excelisfun Wow you know you did good if Geert learned something new :)

@excelisfun

11 ай бұрын

@@josh_excelRight : ) Especially since I have learn so much from Geert already!!!

@GeertDelmulle

11 ай бұрын

@@excelisfunMike, bad news. I tried both INDEX and XLOOKUP as a wrapper function with the intent to convert a array into a range for SUMIFS to handle properly, but it doesn't work. So, no game changer, after testing. I sent you my test file that shows my findings step by step.

@excelisfun

11 ай бұрын

@@GeertDelmulle, Thanks for the report, Geert!

Thanks Mike, I'll be using a various of sumifs tomorrow to build an inventory scanning solution. Now get out there and ride today. Hopefully your weather is good for BMX Sunday.

@excelisfun

11 ай бұрын

Cool! Have fun creating the inventory scan with all the SUMIFS and SUMIF knowledge : ) Thanks for the BMX Bike well wishes. Yesterday, Saturday, I raced the last Washington state qualifier race and did well. Monday, on my way down to taking care of my elderly Mom, I get to stop at a cool skate park in Oregon : )

Easy to follow. And works as advertised. 😂 Awesome!!!

Token of Gratitude! Best Wishes!

@excelisfun

11 ай бұрын

Thank you very much for your kind donation!!!! : ) : )

Thanks Mike for this EXCELlent video.

@excelisfun

11 ай бұрын

You are welcome!!!!

great video thank you so much. I love this format of people sending requests and you showing how it's done, it is much better than watching random tutorials. Keep them coming!

@excelisfun

11 ай бұрын

Glad you like this : ) : )

Many thanks, Mike. That's a subtle distinction: Range Array 🤔

@excelisfun

11 ай бұрын

Big time subtle : ) : ) You are welcome, Ian!!!

Excellency yours mike. Thanks a lot.

@excelisfun

11 ай бұрын

You are welcome a lot : ) : )

Thanks Mike, another awesome video!

@excelisfun

11 ай бұрын

You are welcome, Most Consistent and Amazing Teammate Chris M!!!!!

Nice!!!finally i have understood why sometime i failed using an array formula nested in SUMifs...thanks a lot Mike for this great clarification

@excelisfun

11 ай бұрын

Yes!!! I am so glad this helps!!! I have many other videos on this topic, but this is why I keep sometimes making new videos on old topics : ) : )

Great tip Mike, here's another possible solution: =SUMIF(TAKE(TAKE(E2:P5,XMATCH(E16,D2:D5)),-1),">"&E17)

@excelisfun

11 ай бұрын

Very cool formula : ) : ) Take Take lol

@ankursharma6157

11 ай бұрын

Interesting Solution @oscarmendez-roca9181 . . Thank You for Sharing! Best Wishes!

Amazing Sir ❤

@excelisfun

11 ай бұрын

Glad it is amazing for you!!!!

Thank you Mike, that was some great information on lookups!

Great Sir.

@excelisfun

11 ай бұрын

Glad you like it!!!!

Great video, I learned something today about SUMIF (Honestly i put sumif to the "lost cause" basket of excel functions, cos there are better ways to sum up data. also: In Poland we have an Old saying: "Where devil can't, he will send a hen" well if you traanslaate it to excel: Where SUMIF Cant he will send SUM" Cos with a little effort you can write any SUM function as if it was SUMIFS, so anytime I had some problems (which i didnt understand why they wont work) I used SUM Function and it started to work.

@excelisfun

11 ай бұрын

Always glad that you can learn some new fun things!

Thank u Mike for this awesome video. I encountered the same issue using the filter followed by the SUMIFS function inside of the let function and didn't work since the filter function create an array. I posted the question on Mr Excel message board and I was advised to use the MMult function inside of the let and it worked perfectly

@excelisfun

11 ай бұрын

MMULT is the old way we used to do it. INDEX was an old way to avoid MMULT. But XLOOKUP delivers the array we need in SUMIFS and SUMIF : )

Superb sir ❤

thanks you

What's the difference between a range and array?

@excelisfun

11 ай бұрын

In my array formula videos I cover this, The best one, with pdf notes, that explain it all is MECS video #9: kzread.info/dash/bejne/aI6isbaAm8-8o7g.html Array = 2 or more items Array can be: range array constant resultant array dynamic spilled array Range can be be: range of cells. This difference matters because internally, Excel treats arrays and ranges differently.

@sunilvijay365

11 ай бұрын

​@@excelisfun¹1

Thanks for this theoretical part ! I see now for the first time that the 3rd argument of SUMIF is optional, never noticed this ! 😉 I still find it difficult to understand what the difference between an array and a range is.....

@excelisfun

11 ай бұрын

Excel interprets an array and range differently: Array = 2 or more items Array can be: range array constant resultant array dynamic spilled array Range can be be: range of cells. This difference matters because internally, Excel treats arrays and ranges differently.

Thank you, my teacher, and here is a point that I hope everyone knows The problem with the SUMIFS, SUMIF, COUNTIFS, COUNTIF, OFFSET, RANK, MAXIFS, MINIFS and some other functions is that they work in some PARAMETER according to the principle of Call by Address Therefore, they cannot calculate values that come from formulas, and only calculate what comes from a real reference, such as the cell or the defined name, or what happened in the Clip, which is that it is the result of a completely real reference formula. If we imagine that the result of XLOOKUP some cells without others, SUMIFS will not produce a result, On Other hand most Functions in Excel work by Call by Value Principle So I hope from all Excel Community demand from MICROSOFT make Improved versions of all functions that run on the Call by address principle To Call by Value And in the future, they don't put functions in Excel that work on this principle

Amazing

@excelisfun

11 ай бұрын

Glad it is amazing for you : )

Great video Mike can you make video about python in excel

@excelisfun

11 ай бұрын

I will not have one out anytime soon. I don't know python, yet ; ) Currently I am writing two books: one about M Code and one about Dynamic Array Formulas. Plus, I am taking care of my elderly Mom. I have no time at all... : ( : ( I can't wait, but it will be a while.

Thanks Mike. That was very interesting!!! :) :)

@excelisfun

11 ай бұрын

The Formula Guy already new this ; )

Thank you so much! I've paused the video for exercise and i've tried to propose my solution. Using FILTER 3 times inside COUNTA you can get the right answer. In my sheet the main table formed by 4 months starts in B2 while the month to choose is in I2 with the hurdle in I4: COUNTA(FILTER(FILTER(B3:E21;B2:E2=I2);FILTER(B3:E21;B2:E2=I2)>I4)) Maybe with LET would be possible to to improve

@excelisfun

11 ай бұрын

Very cool formula!!!

@carlo_migliari81

11 ай бұрын

@@excelisfun Thanks! With LET is easier to read: =LET( myrange;FILTER($B$3:$E$21;$B$2:$E$2=I2); condition;FILTER($B$3:$E$21;$B$2:$E$2=I2)>I4; COUNTA(FILTER(myrange;condition)))

Hi Mike, Thanks for the video, Can You help if there is any way I can put this into 2D Spilled Formula, For example if I have next to each other Jan, Feb, Mar, Apr, May and I want underneath them to be the sumifs for each month in ONE spilled formula, So basically I want a magical way to be able to make the sum range to be different in each column of the formula (sorry if I am poorly explaining it, if not clear let me know I can share an excel file)

Hi Mike, Is there a way I can generate a unique range of 9 digit alpha numeric numbers ?

How would I do it if I need another criteria say in column A that breaks the numbers into different categories? I tried to use the range solution but it didn't like me adding a criteria in a different column.

Could the Microsoft Excel team update the sumifs function to allow arrays? It would be great for example to use choosecols to retrieve columns 1 and 3 and use sumifs to get a sum greater than 45.

Hello mike, can i ask.. i get problem with calculated field in pivot table why cannot use ?

can we have Alternative Spill array nested formulas for Video Number 735 (Subtotal(9,Offset(,,indirect(row("1:"&colums().........) without using volatile offset function?

@soumithripavan9260

9 ай бұрын

* (Subtotal(9,Offset(,,row(indirect("1:"&colums().........)

Side comment: add picture/cut out of yourself on video your thumbnail. Maybe add some emotions/gestures. That might help with youtube algorithm. Mr.Excel, Leila Gharani, Mirinda etc all do that

@excelisfun

11 ай бұрын

Thank you for that advice : )

Great video Mike can you make video about python in excel