In this post I would like to present a solution to the situation in which we wish to count the number of rows for which a stipulated condition is met in at least one of several columns.
To illustrate what is meant by this, consider the extract below:
which details levels of scrap nickel exports for various countries and for various years (you can download the workbook here).
I recently received a request from James, who was interested in a formula-based solution to the following problem: given a two-dimensional range containing a mixture of numbers and empty cells (which I am defining as being either “genuinely” empty or as containing the null string “” as a result of formulas in those cells), generate a unique list of those numbers in order of their frequency within that range, with the most frequent first. What’s more, if two or more numbers occur the same number of times within that range, then they should be listed in order of their size from smallest to largest.
For example, for the dataset in A1:F6 below, we would return the list as given beginning in I1.
Some of us may be familiar with the standard technique using INDEX, SMALL, etc. which, given a single-column or single-row array, we can use to return a list of only those values which satisfy one or more criteria of our choosing.
In a previous post (see here) I outlined a method which, given a range consisting of more than one column, returned a single column consisting of all non-blank entries from that range. It can easily be verified that the single condition within this formula (i.e. that the entry be non-blank) can be extended to multiple criteria and so, effectively, we now have at our disposable the means with which to generate single-column lists from both one- and two-dimensional arrays.
But can we go one further yet again? “Three-dimensional” is the collective term often applied to those formulas in Excel which are capable of operating over not just single columns or rows, nor yet ranges consisting of multiple columns or rows (two-dimensional), but which also function effectively over multiple worksheets.
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: