Excel Magic Trick 1042 Array Functions Housed In Other Functions Usually Don't Need Ctrl Shift Enter

Leonid Koyfman at YouTube shows great trick Unique Count for numbers formula that does not require Ctrl + Shift + Enter.
See:
1. Count Unique Numbers formula that uses SUM and FREQUENCY and IF functions and does not require Ctrl + Shift + Enter.
2. Count Unique Numbers formula that uses SUM and FREQUENCY and SIGN functions and does not require Ctrl + Shift + Enter.
3. Count Modes formula that uses COUNT and MODE.MULT functions and does not require Ctrl + Shift + Enter.
4. Add result of MMULT function formula that uses SUM and MMULT functions and does not require Ctrl + Shift + Enter.
5. Add result of TREND function formula that uses SUM and TREND functions and does not require Ctrl + Shift + Enter.

Array Functions Housed In Other Functions Usually Don't Need Ctrl Shift Enter.

Distinctions:

1) Array formulas are formulas that contain an operation or function that produces an array of items. Array formulas can deliver multiple items or a single item.
2) Array operation is the act of operating on an array and then delivering a resultant array.
3) Array Functions are Excel built-in functions that make array operations.

[excelisfun]

It is true, our Online Excel Team is trying to bring array formulas aspects and elements out into the light!!!

Go Team!!!

[excelisfun]

Yes, of course it does: because A1:C1=8 is an array operation that produces an array, but in the example above the FREQUENCY function is a built in Array Function. It is Array Functions that display the behavior of not requiring Ctrl + Shift + Enter when inside another function.

[excelisfun]

Array formulas, array operations, Array Functions are all different.

Array formulas are formulas that contain an operation or function that produces an array of items. Array operation is the act of operating on an array and then delivering a resultant array. Array Functions are Excel built-in functions that make array operations.

[ExcelStrategy]

We are bringing the knowledge of array formulas to the limits !

[ExcelStrategy]

Totally true ! When I wrote "array formulas" I was referring to the complessive improvement of the array field :)

[excelisfun]

Array Functions just seem to work if they are in other functions - no Crtl + Shift + Enter.

[nadermounir]

Nice video especially the sign function 😀

[krn]

Weird, guess that goes to show there are many different ways to get to the right answer.

[RajKothari]

I find that a lot of time that the formula would not work without CSE and throws a value error, for example in this formula : =SUM(IF(A1:C1=9, 8, IF(A1:C1=0, 8, A1:C1))) I had wrapped the array with the sum but still requires CSE.