The idea of being forced to choose just three Excel functions is a horrifying thought. Would I still be allowed to write my own functions in VBA and COM Addins or is that cheating?
Could it be that sumifs/countifs and other functions that accept ranges but not arrays should be provided a 2D array? Like when you get the values of a range in vba, it is ALWAYS a 2D array, even if the range has only a column or only a row. When a human enters an array of values, more often than not it is in the form of a vector, a 1D arrray. Actually, for sumifs/countifs your ranges are either columns or rows (never "rectangular"). Maybe that's why the functions "complain"?
Come to think of it... Using notation like ={1;2,1;3} you CAN'T provide a 2D array of just a column or just a row. In vba you can build single column/row 2D arrays but even if you use worksheetfunction.sumifs you will get an error if you don't provide ranges. So the only way to make it work is for Microsoft to change the behaviour of the function, so that it accepts either ranges or arrays. It shouldn't be that hard, if user enters a range(s), get the values from the range(s), then do the usual checks about same size for all columns/rows, and then provide the results...
@@rubenmunozverdu7528- SUMIFS doesn’t work across closed workbooks - so that would also indicate that it must be an actual range. When a function has been around for a long time, it’s difficult to change it because it’s difficult to know if it will break a calculation that somebody is relying on. So, it’s probably harder than we think.
Love the new jingle 👍❤❤
😁 great!
The idea of being forced to choose just three Excel functions is a horrifying thought. Would I still be allowed to write my own functions in VBA and COM Addins or is that cheating?
Cheating - without a doubt. 😁
@@ExcelOffTheGrid Noooooooo! 😫
@@excelrobot why not? 🤣 - restraint is beautiful, it invites to hack 😁, not cheat.
Could it be that sumifs/countifs and other functions that accept ranges but not arrays should be provided a 2D array? Like when you get the values of a range in vba, it is ALWAYS a 2D array, even if the range has only a column or only a row. When a human enters an array of values, more often than not it is in the form of a vector, a 1D arrray. Actually, for sumifs/countifs your ranges are either columns or rows (never "rectangular"). Maybe that's why the functions "complain"?
Come to think of it... Using notation like ={1;2,1;3} you CAN'T provide a 2D array of just a column or just a row. In vba you can build single column/row 2D arrays but even if you use worksheetfunction.sumifs you will get an error if you don't provide ranges. So the only way to make it work is for Microsoft to change the behaviour of the function, so that it accepts either ranges or arrays. It shouldn't be that hard, if user enters a range(s), get the values from the range(s), then do the usual checks about same size for all columns/rows, and then provide the results...
@@rubenmunozverdu7528- SUMIFS doesn’t work across closed workbooks - so that would also indicate that it must be an actual range.
When a function has been around for a long time, it’s difficult to change it because it’s difficult to know if it will break a calculation that somebody is relying on. So, it’s probably harder than we think.