Cat On A Spreadsheet

Once a dynamic-array formula can filter a dataset and enrich it with information from elsewhere, the next natural question is what we can do with the resulting data. In many reports, retrieving individual records is only the beginning. The real purpose of the report is to turn those records into something that tells us what happened: how many transactions occurred, how much was sold, which products performed best, which customers generated the most activity, or how one group compares with another. That means moving from individual records to groups. This is where functions such as UNIQUE, SUMIFS, COUNTIFS, MAP, BYROW and SORTBY become particularly useful. They allow us to take a dataset containing many individual records and progressively transform it into an aggregated result.

The distinction between filtering and grouping is worth making clear. FILTER answers a question such as “Which records meet this condition?” Grouping answers a different question: “What are the distinct groups represented by these records, and what does each group contribute?”

Suppose our transaction table contains a product code and quantity:

The result is a spilled array containing one instance of every product code that appears in the transaction table. On its own, that is useful, but it is not yet a report. We now have the groups we want to analyse. The next step is to calculate something for each group.

For example, we might want the number of transactions associated with each product:

The important part of this formula is that COUNTIF is being given an array of criteria rather than one criterion. If Products contains P001, P002, P003 and P004, COUNTIF returns the corresponding count for all four products. The result is therefore another array, which we can combine with the product codes using HSTACK.

This is another example of the central idea behind this series: Excel does not necessarily need us to perform the same calculation separately for every row. If a function can accept an array as an argument, it can often perform the operation across the entire collection.

We can do exactly the same thing with quantities. If we want the total quantity sold for each product, we can replace COUNTIF with SUMIF:

At this point we have something that already resembles a conventional summary report. But dynamic arrays give us considerably more control over how that report is constructed.

Suppose we want both transaction count and total quantity:

The result is now a three-column array. The first column identifies the group, the second describes how many records belong to it, and the third gives us the aggregate quantity. The next obvious question is whether we can sort that result - we can, and this is where SORTBY becomes particularly valuable.

Suppose we want the products with the highest quantities at the top:

The -1 tells SORTBY to sort in descending order.

Notice something important here. We are not sorting the quantity column and then trying to make the other columns follow it. We sort the entire Report array using Quantity as the sorting array. That distinction prevents one of the classic problems associated with spreadsheet reporting: accidentally sorting one column independently from the records to which it belongs.

We can now take the next step and assign a rank. A common instinct would be to use RANK or RANK.EQ, but there is another way of thinking about the problem. If our report is already sorted in descending order, the position of each row effectively represents its rank. We can generate that position with SEQUENCE:

If there are ten products, SEQUENCE(ROWS(Report)) produces the numbers 1 through 10. HSTACK then adds those numbers to the beginning of the report.

This is a small example, but it illustrates a useful reporting principle. Sometimes we do not need a specialised function for every part of a calculation. We can derive information from the structure of the array itself.

There is, however, a difference between position and rank. If two products have exactly the same quantity, the report above will still give them different positions. One will be rank 1 and the other rank 2, even though they are tied. If the business requirement is to assign equal ranks to equal values, then RANK.EQ is more appropriate:

The dynamic-array equivalent can be constructed by passing the entire array of quantities:

Now equal quantities receive equal ranks.

This also demonstrates why it is worth separating the concepts of sorting and ranking. Sorting determines the order in which records are displayed. Ranking assigns a numerical relationship between values. They often appear together in reports, but they are not the same operation.

The same approach can be applied to dates. Imagine that the transaction table contains a date, product code and quantity, and we want a daily summary. Instead of using the product code as our grouping field, we extract the unique dates:

We now have a daily report generated entirely from the transaction table. But real reporting requirements are rarely that simple. Usually there are additional conditions. Perhaps we only want transactions from August 2026. Perhaps we only want a particular department. Perhaps the report needs to consider transactions above a particular value. This is where the combination of FILTER and aggregation becomes especially powerful.

Suppose the transaction table contains a Department column and we want a product summary for one selected department. We can filter the transaction data first:

The logic is now becoming quite clear. FILTER establishes the population we care about. UNIQUE establishes the groups within that population. SUMIF calculates the aggregate for each group. SORTBY determines the presentation order.

That sequence is worth remembering because it occurs repeatedly in real reporting work: filter the population, identify the groups, calculate the measures, then order the result.

There is another situation where this pattern becomes useful: calculating more complicated measures than a straightforward SUMIF or COUNTIF. Suppose we want the average transaction quantity for each product. We can use AVERAGEIF:

But sometimes the calculation we need does not have a convenient ...IF equivalent. This is where newer array functions such as MAP become particularly interesting.

MAP allows us to take an array and apply a calculation to each element of it. Conceptually, it is very similar to saying, “For each item in this array, perform this operation and return the result.”

For example:

Here MAP takes each product code in turn and passes it to the LAMBDA. The LAMBDA then filters the transaction values belonging to that product and calculates their average.

This is a significant conceptual step because we are no longer limited to the calculations for which Excel happens to provide a convenient conditional aggregation function. We can define the calculation we want and apply it to every group.

That becomes even more useful when the measure depends on several conditions. For example, imagine that each transaction has a value and a status, and we want the average value of completed transactions for every product. We could use:

The multiplication of the two logical tests creates an AND condition. Both conditions must be true for a record to enter the filtered array. This is a useful pattern, but it also illustrates why dynamic-array formulas should not be made complicated merely because Excel allows us to do so. If a simple AVERAGEIFS expresses the requirement clearly, it will usually be preferable. The more advanced construction becomes valuable when the calculation itself is more complex or when we need the flexibility of LAMBDA.

Once we have grouped and aggregated data, we can also use the result to answer questions about the groups that did not appear. This is particularly useful in operational reporting.

Suppose the product master contains 100 active products, but only 73 appeared in the transaction data during the reporting period. UNIQUE(tblTransactions[ProductCode]) tells us which products did appear, but it does not directly tell us which products did not. We can use FILTER against the master list:

This reverses the usual lookup mindset. Instead of taking transaction records and enriching them with product information, we take the master list and ask which master records have no corresponding transaction.

That is an important reporting technique because absence is often as meaningful as presence. A sales report may ask which products sold the most, but an operational report might instead need to identify products that have not sold at all. A staffing report might need employees with no recorded activity. An inventory report might need locations with no movement. In each case, the dynamic-array machinery is fundamentally the same: establish one population, compare it with another, and return the relevant subset.

At this point we have moved considerably beyond the traditional idea of a formula being attached to a particular cell. The formula is becoming a description of a reporting process: we start with a raw dataset, we establish the population of interest, we identify its distinct groups, we calculate one or more measures for each group, and we can then sort, rank, filter or compare the resulting array. And because the output is dynamic, adding a new transaction does not require us to copy formulas down another row or extend a manually maintained summary range. The underlying dataset changes, the arrays recalculate, and the report changes with it.

That is particularly valuable when the report is intended to be reused rather than produced once. A well-designed dynamic-array report can become a small analytical layer sitting on top of the underlying data.

There is still one important limitation to address, however. So far, our grouping has generally been performed against a single dimension: product, date, department or another individual field. Real reports often need to combine several dimensions at once. We may need sales by department and product, transactions by month and status, or activity by customer and region. At that point, we need to think about how to construct and analyse combinations of fields rather than simply extracting unique values from one column. That takes us naturally into the next stage of the series: combining dynamic datasets and comparing their structure, where the ability to manipulate entire arrays becomes even more powerful.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet