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)
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
@@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.
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.
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 : )
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!
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
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
In my array formula videos I cover this, The best one, with pdf notes, that explain it all is MECS video #9: ruclips.net/video/6ZnOTGklVsU/видео.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.
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.....
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.
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.
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 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)))
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.
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.
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)
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
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)
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
@@excelisfun Wow you know you did good if Geert learned something new :)
@@josh_excelRight : ) Especially since I have learn so much from Geert already!!!
@@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.
@@GeertDelmulle, Thanks for the report, Geert!
Clipboard trick. Key, especially when building a large LET() formula. Everything right there, like a restaurant menu. Thank you!
Yes!!!!! Restaurant menu simile is perfect : ) : ) : )
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.
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 : )
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!
Glad you like this : ) : )
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
Great tip Mike, here's another possible solution:
=SUMIF(TAKE(TAKE(E2:P5,XMATCH(E16,D2:D5)),-1),">"&E17)
Very cool formula : ) : ) Take Take lol
Interesting Solution @oscarmendez-roca9181 . . Thank You for Sharing!
Best Wishes!
Many thanks, Mike. That's a subtle distinction: Range Array 🤔
Big time subtle : ) : ) You are welcome, Ian!!!
Nice!!!finally i have understood why sometime i failed using an array formula nested in SUMifs...thanks a lot Mike for this great clarification
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 : ) : )
Easy to follow. And works as advertised. 😂 Awesome!!!
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
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 : )
Thanks Mike for this EXCELlent video.
You are welcome!!!!
Token of Gratitude!
Best Wishes!
Thank you very much for your kind donation!!!! : ) : )
What's the difference between a range and array?
In my array formula videos I cover this, The best one, with pdf notes, that explain it all is MECS video #9: ruclips.net/video/6ZnOTGklVsU/видео.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.
@@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.....
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.
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.
Always glad that you can learn some new fun things!
Thanks Mike, another awesome video!
You are welcome, Most Consistent and Amazing Teammate Chris M!!!!!
Excellency yours mike. Thanks a lot.
You are welcome a lot : ) : )
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
Very cool formula!!!
@@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)))
Thank you Mike, that was some great information on lookups!
Amazing Sir ❤
Glad it is amazing for you!!!!
Great Sir.
Glad you like it!!!!
Thank you
You are welcome!!!
thanks you
Superb sir ❤
Amazing
Glad it is amazing for you : )
Great video Mike can you make video about python in excel
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!!! :) :)
The Formula Guy already new this ; )
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.
Hi Mike, Is there a way I can generate a unique range of 9 digit alpha numeric numbers ?
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)
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?
* (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
Thank you for that advice : )
Great video Mike can you make video about python in excel