We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:
1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)
2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas
3) We may simply be interested from a theoretical point of view
In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.
This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” critieria.
For example, given the following data:
