Monday Morning Mulling: June 2022 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

The challenge this month was to calculate the running total despite the filter.  Easy, yes? 

The Challenge

The issue this month may be common and useful for anyone who uses Excel to produce cumulative/running total reports frequently.  You can download the original question file here.

This month’s challenge was to write a formula on a new column of the provided table to calculate the running total of column Number regardless of whether the Excel Table (i.e.created using Insert -> Table or CTRL + T) is filtered or not.  The result should look like the column Total generated on the right (below):

As always, there were some requirements:

• the formula needed to be within just one column (no “helper” cells)
• this was a formula challenge; no Power Query / Get & Transform or VBA!
• the formula still calculated the correct running total when the table was filtered.

Suggested Solution

You can find our Excel file here which demonstrates our suggested solution.

Normally, we would think of using SUM to sum up the range from the top row to current row to calculate the running total.  However, the result will not be adjusted if the table is filtered, especially if the first row goes walkabout.

Hence, we think of using an Excel function that ignores any rows that are not included in the result of a filter.  That function is SUBTOTAL.

Firstly, the current row of column Number can be easily identified using the Table reference as below.

You may recall the @symbol denotes the implicit intersection and literally means the value of the ‘Number’ field for the row you are on.

Secondly, we can easily select a range from the top row to current row and anchor the top row reference.  However, it is not dynamic enough when a top row can be added or deleted.  Therefore, we use INDEX to find the top cell of the ‘Number’ field as follows.

As you may know, INDEX returns not only a value but also a cell reference.  This means you can use it along with a colon “:” to refer to a range of cells.  The range that we need for the running total is as follows:

Finally, we then use SUBTOTAL to generate the running total.  SUBTOTAL ignores all filtered rows regardless of which function number in the first argument is used.

The number nine [9] represents the SUM function including hidden rows while number 109 works similarly but ignores hidden rows.  These hidden rows need to be caused by either hiding or grouping (not filtering).  Depending upon your purpose for the running total, you can choose which number to use.

The Final Friday Fix will return on Friday 29 July 2022 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.