Clipboard trick. Key, especially when building a large LET() formula. Everything right there, like a restaurant menu. Thank you!
@excelisfun
Жыл бұрын
Yes!!!!! Restaurant menu simile is perfect : ) : ) : )
@GeertDelmulle
Жыл бұрын
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
Жыл бұрын
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
Жыл бұрын
@@excelisfun Wow you know you did good if Geert learned something new :)
@excelisfun
Жыл бұрын
@@josh_excelRight : ) Especially since I have learn so much from Geert already!!!
@GeertDelmulle
Жыл бұрын
@@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
Жыл бұрын
@@GeertDelmulle, Thanks for the report, Geert!
@mattschoular8844
Жыл бұрын
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
Жыл бұрын
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 : )
@rielcuartero3828
11 ай бұрын
Easy to follow. And works as advertised. 😂 Awesome!!!
@makeittrades5750
Жыл бұрын
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
Жыл бұрын
Glad you like this : ) : )
@SyedMuzammilMahasanShahi
Жыл бұрын
Thanks Mike for this EXCELlent video.
@excelisfun
Жыл бұрын
You are welcome!!!!
@Reduce_Scan
Жыл бұрын
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
@iankr
Жыл бұрын
Many thanks, Mike. That's a subtle distinction: Range Array 🤔
@excelisfun
Жыл бұрын
Big time subtle : ) : ) You are welcome, Ian!!!
@ankursharma6157
Жыл бұрын
Token of Gratitude! Best Wishes!
@excelisfun
Жыл бұрын
Thank you very much for your kind donation!!!! : ) : )
@oscarmendez-roca9181
Жыл бұрын
Great tip Mike, here's another possible solution: =SUMIF(TAKE(TAKE(E2:P5,XMATCH(E16,D2:D5)),-1),">"&E17)
@excelisfun
Жыл бұрын
Very cool formula : ) : ) Take Take lol
@ankursharma6157
Жыл бұрын
Interesting Solution @oscarmendez-roca9181 . . Thank You for Sharing! Best Wishes!
@lucaviglio1206
Жыл бұрын
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
Жыл бұрын
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 : ) : )
@nadermounir8228
Жыл бұрын
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
Жыл бұрын
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 : )
@nsanch0181
Жыл бұрын
Thank you Mike, that was some great information on lookups!
@Bhavik_Khatri
2 ай бұрын
Thank you
@excelisfun
2 ай бұрын
You are welcome!!!
@chrism9037
Жыл бұрын
Thanks Mike, another awesome video!
@excelisfun
Жыл бұрын
You are welcome, Most Consistent and Amazing Teammate Chris M!!!!!
@khanabdussabur8604
Жыл бұрын
Excellency yours mike. Thanks a lot.
@excelisfun
Жыл бұрын
You are welcome a lot : ) : )
@ExcelInstructor
Жыл бұрын
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
Жыл бұрын
Always glad that you can learn some new fun things!
@barttitulaerexcelbart9400
Жыл бұрын
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
Жыл бұрын
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.
@carlo_migliari81
Жыл бұрын
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
Жыл бұрын
Very cool formula!!!
@carlo_migliari81
Жыл бұрын
@@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)))
@dharmendarrana4191
Жыл бұрын
Great Sir.
@excelisfun
Жыл бұрын
Glad you like it!!!!
@shubhampawar8506
Жыл бұрын
Amazing Sir ❤
@excelisfun
Жыл бұрын
Glad it is amazing for you!!!!
@fernando5166
Жыл бұрын
thanks you
@vijaysahal4556
Жыл бұрын
Superb sir ❤
@johnborg5419
Жыл бұрын
Thanks Mike. That was very interesting!!! :) :)
@excelisfun
Жыл бұрын
The Formula Guy already new this ; )
@mountainhiker8523
Жыл бұрын
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.
@sa51421
Жыл бұрын
Amazing
@excelisfun
Жыл бұрын
Glad it is amazing for you : )
@ivorilanahorwitz9300
Жыл бұрын
What's the difference between a range and array?
@excelisfun
Жыл бұрын
In my array formula videos I cover this, The best one, with pdf notes, that explain it all is MECS video #9: kzitem.info/news/bejne/l5CktYh9pJ6Hpoo 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
Жыл бұрын
@@excelisfun¹1
@desilvakts2071
5 ай бұрын
Hi Mike, Is there a way I can generate a unique range of 9 digit alpha numeric numbers ?
@TheAlwaysGoel
Жыл бұрын
Great video Mike can you make video about python in excel
@excelisfun
Жыл бұрын
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.
@heshamfouad8886
Жыл бұрын
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)
@maxzhao3514
6 ай бұрын
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.
@ferrifahd2117
Жыл бұрын
Hello mike, can i ask.. i get problem with calculated field in pivot table why cannot use ?
@soumithripavan9260
Жыл бұрын
can we have Alternative Spill array nested formulas for Video Number 735 (Subtotal(9,Offset(,,indirect(row("1:"&colums().........) without using volatile offset function?
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
Жыл бұрын
Thank you for that advice : )
@TheAlwaysGoel
Жыл бұрын
Great video Mike can you make video about python in excel
Пікірлер: 68