Basic Concepts
Most of the Excel functions that take an array as an argument don’t require that this array be a cell range. E.g. the formula =STDEV.S(A1:A10) will return the standard deviation of the values in cell range A1:A10. You can also use the array formula =STDEV.S(A1:A10-B1:B10). This will return the standard deviation of the pairwise differences between the cells in A1:A10 and B1:B10. Thus, although STDEV.S is not an array function, the fact that the array formula =A1:A10-B1:B10 is used as an argument makes =STDEV.S(A1:A10-B1:B10) an array formula.
Another example is =SUM(MINVERSE(A1:C3)). This array formula finds the inverse of the matrix A1:C3 and then sums up the 9 values in this matrix.
Arguments restricted to cell ranges
The following Excel functions are an exception, in that they only take arguments that are a cell range and don’t permit combinations such as those described above: COUNTIF, COUNTIFS, SUMIF, SUMIFS, MAXIFS, MINIFS, AVERAGEIF, AVERAGEIFS, RANK, RANK.EQ and RANK.AVG.
Thus, we cannot create array formulas such as =RANK(A1,TRANSPOSE(A1:A10)) or =COUNTIF(A1:A10*2, 5).
The Real Statistics Resource Pack provides the COUNT_IF, SUM_IF, AVG_IF, and RANK_AVG worksheet functions which provide similar functionality and do accept arrays as arguments that are not necessarily cell ranges. See Real Statistics Conditional Functions and Ranking Functions in Excel for details.
Observations
Note too that formulas such as =AVERAGE(A1:A10) and =AVERAGE(A1:A10*1) yield the same value, as expected, at least when range A1:A10 contains only numeric data. But while =AVERAGE(A1:A10) outputs the mean of A1:A10 ignoring empty cells and cells containing alphanumeric data, =AVERAGE(A1:A10*1) does not produce the correct value when A1:A10 contains non-numeric cells (since blank cells are treated as zero in this case).
Reference
Microsoft (2021) Excel functions
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm19