Running counts

Running counts are useful for keeping track of how many times a value has occurred up to a certain point.

There are multiple ways of doing this, so let’s look at some traditional and modern methods.

Example

On page 1, a table called tblCoffee contains 12 coffee products stocked at the UK supermarket Tesco, featuring columns for their names, brands, and prices.

Page 2 features two examples based on an incremental number sequence that restarts when the brand name changes.

The traditional method uses an IF formula for each row to check if the entry above is not equal to the current one. If TRUE, 1 returns; otherwise, 1 is added to the previous row’s value.

In contrast, the modern method consists of a single formula that combines SCAN, LAMBDA, IF and OFFSET. The LAMBDA statement has two parameters for the accumulator and value: a and b. With the help of OFFSET, the IF statement checks if the row value above b equals b. If so, add 1 to a; otherwise, return 1.

Both of these methods work, but they reset to 1 when a new value appears, meaning each batch of values must be placed together for an undisrupted sequence. What if you have scattered values?

Page 3 provides a solution for this. The traditional method uses multiple COUNTIF formulas to tally the number of instances a brand appears in the range. This expands dynamically due to the first and second half of the reference being absolute and relative, respectively.

Once again, the modern approach replicates it using a single formula. Nested inside the COUNTIF function is an OFFSET statement, which starts a reference from the top row of Brand. SEQUENCE(ROWS(tblCoffee[Brand])) ensures the total rows in the range increment upwards as the formula cycles downwards.

Final thoughts

Be aware that only the traditional methods work inside tables, as dynamic arrays are not supported. I hope this changes in the future, as I’d love to combine the benefits of both.