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” criteria.
For example, given the following data:
Last week I set readers the challenge which can be found here.
Three solutions were offered, two of which from the same person, and both of which were correct! So many congratulations to Bill on successfully solving what was quite a complex challenge!
Indeed, as Ben Schwartz pointed out, this challenge appears to have been set previously on the internet, and seems to have been only partially solved on those occasions. In any case, thanks also to Ben for his suggestion, which he confesses was cobbled together from those previous solutions he found, and which worked in all but a few exceptional cases.