The question essentially is whether built-in conditional functions in Excel such as SUMIF(), COUNTIF(), AVERAGEIF() but also SUBTOTAL() and AGGREGATE() are computationally more efficient, i.e. lighter, than using their unconditional variants (SUM(), COUNT(), etc.) in combination with array calculations and simple operators (*, +, ...).
Moreover, can this even be answered generally or does it depend on further factors such as the class (string vs numeric)?
An example should make things clearer. Suppose we have the following table, where the data ranges in columns B, C and D are named 'CriteriaRange1', 'CriteriaRange2', and 'ValueRange', respectively:
The formula used in H8 is =SUMIFS(ValueRange,CriteriaRange1,"="&H3,CriteriaRange2,">"&H4), i.e. using the built-in SUMIFS() function
and the formula used in H9 is =SUM(ValueRange*(CriteriaRange1=H3)*(CriteriaRange2>H4)), i.e. using the unconditional SUM() function in combination with array multiplication.
Can it be generally said that one is computationally more efficient. And if not, does it depend on the value class, e.g. string vs numeric as represented by 'Criteria Range 1' and 'Criteria Range 2' in the example above. Or can it just not be generalized that easily at all?
Edit 1:
I created an example sheet and copied the microtimer function for more precise timing of calculations from this Webpage. On this example sheet I timed the calculation times of the built-in functions SUMIF(), AVERAGEIF() and COUNTIF() against the array versions for the same task, e.g. SUM(X*(X>Y)). Exact functions used shown here.
The results were the following:
For the functions tested, it is pretty safe to say that the built-in versions were consistently more efficient than array calculations from a limited empirical perspective. Moreover, it seems the calculation times of the built-in functions vary significantly less across the functions than for the array counterparts.
It would still be great to get an answer from someone who is familiar with computation methodology deployed by Excel to explain this in a more generalized manner.