Have you tried adding a running total in a Google Sheets Pivot Table without success? Here are the reasons and how to do it:

Before adding a running total to a Google Sheets Pivot Table, consider these key points, which we will discuss in detail later in this tutorial:

1. Ensure your source data is sorted in the same order as the grouping fields in the Pivot Table for accurate running total calculations.
2. Insert a helper column in the source data to display the running total in the Pivot Table. This column will feature an array formula in the topmost cell.
3. Place fields only in the ROWS section of the Pivot Table. Include fields in both ROWS and COLUMNS sections only if you’re comfortable with having blank cells between running totals.

For instance, consider two categories: A and B. A has production in January, February, April, and May, while B has production throughout January to May, each with a production quantity of 5 tons per month. The resulting running totals in the Pivot Table may appear as follows:

However, note that the value in the blank cell (March) should ideally be 10, carrying forward the previous month’s value. Unfortunately, this won’t happen.

Be aware that addressing these considerations is crucial for successfully adding a running total to a Google Sheets Pivot Table.

Advantages of Including a Running Total in a Google Sheets Pivot Table

1. Facilitates Easy Comparison:
   • Adding a running total allows for straightforward comparisons of cumulative values across different categories or groups.
2. Data Expansion and Collapse:
   • Users can expand or collapse data (drill down) within a Pivot Table, offering flexibility in data exploration.
3. Utilization of Pivot Table Grouping Feature:
   • If your source data includes a date field (column), the “Create a pivot table group” feature within the Pivot Table can be utilized. The cumulative sum will automatically respond to this grouping.
4. Sequential Understanding of Trends:
   • These features collectively assist in understanding trends and patterns sequentially, providing valuable insights into the evolving data.

By incorporating a running total and utilizing the features of a Pivot Table in Google Sheets, users can enhance their ability to analyze, compare, and interpret data trends more effectively.

I’ll walk you through two examples to help you get acquainted with adding a cumulative sum to Google Sheets Pivot Tables.

The use of two examples is crucial as the levels of grouping in the ROWS and COLUMNS sections may vary depending on your source data. Additionally, you may need to adjust columns in the helper column formula based on your specific dataset.

Here is my sample sheet in copy mode:

Sample Sheet

Adding Running Totals in Pivot Tables Grouped By Rows

The provided sample data includes materials supplied for two different projects from distinct sources, along with their quantities. There are also two types of materials: Roadbase and Sand.

Data structure: Project, Material, and Source in columns A to C, and Qty. supplied in column D.

Pivot Table Creation with Cumulative Sum: Step-by-Step Guide

I have organized the steps into four categories: Pivot Table Creation, Data Sorting, Formula Addition, and Final Settings for Adding Running Total in the Pivot Table.

1. Pivot Table Creation

To create the Pivot Table, follow the steps below:

1. Select the range A1:E.
2. Click Insert > Pivot Table.
3. Choose ‘Existing Sheet’ and select G1 in the field below, then click ‘Create.’
4. Add the fields as shown in the screenshot below.

We’ve added Project, Material, and Source while unchecking ‘Show Total’ in each field. You can choose to check or uncheck ‘Repeat row labels’ in these fields as needed.

In addition to the above, I’ve included the ‘Project’ field under the FILTERS section to exclude blank rows from the report. This step is unnecessary if you’re using the actual data range, A1:E19. However, since we’ve utilized a larger range, A1:E1000, encompassing various blank rows, this filtering becomes essential.

Note: If you’re new to Pivot Tables, refer to this tutorial: How to Create a Pivot Table Report to Summarize Data in Google Sheets.

2. Sorting the Source Data

Ensure that you sort the data in the order you added the fields in the Pivot Table—specifically, sort by ‘Project,’ ‘Material,’ and ‘Source.’

1. Select the range A1:D19.
2. Click on Data > Sort range > Advanced sorting range options.
3. Check “Data has header row” and sort by ‘Project,’ ‘Material,’ and ‘Source.’

3. Adding a Formula for the Helper Column

Next, we’ll employ an array formula in the first row of the helper column, here in E2. In this formula, we’ll apply grouping to the fields except the last field in the ROWS section. In other words, we will use the fields ‘Project’ and ‘Material’ in the formula, excluding the ‘Source’ field.

In cell E2, insert the following array formula:

=ARRAYFORMULA(MAP(
A2:A, B2:B,
LAMBDA(a, b, IF(a="", ,
   SUMIFS(D2:D, A2:A, a, B2:B, b, ROW(A2:A), "<="&ROW(a))
))))

The provided formula returns the running total grouped by A2:A and B2:B, excluding C2:C as previously mentioned.

Anatomy of the Formula:

Let’s start with the syntax of the SUMIFS function.

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Where:

• sum_range: D2:D
• criteria_range1: A2:A
• criterion1: a
• criteria_range2: B2:B
• criterion2: b
• criteria_range3: ROW(A2:A)
• criterion3: “<=”&ROW(a)

The formula sums the range D2:D if A2:A equals a, B2:B equals b, and ROW(A2:A) is less than or equal to ROW(a). The identifiers a and b correspond to A2 and B2 in the first row, A3 and B3 in the second row, and so on. The MAP function is utilized for this purpose.

Refer to my tutorial, Running Total with Multiple Subcategories, for an in-depth explanation of the formula. Here, I’ll assist you in editing the formula to add or remove grouping fields.

The formula currently uses two fields (columns) for grouping: A2:A and B2:B. I’ve specified these arrays in MAP as MAP(A2:A, B2:B,, and within SUMIFS, use A2:A, a, B2:B, b,.

If you have one more field (column), for example, C2:C, you can incorporate it as MAP(A2:A, B2:B, C2:C, and A2:A, a, B2:B, b, C2:C, c,.

Follow a similar approach to add more levels to the grouping or remove existing ones.

While adding or removing columns in the formula, ensure consistency in sorting and also within the Pivot Table ROWS section. If there are 4 fields in the ROWS section, there will be 4 columns for sorting and 3 columns in the formula for grouping.

4. Final Steps for Adding Running Totaol in the Pivot Table

Next, go to the Pivot Table editor panel and add the field ‘Running Total’ under the VALUES section. Choose the MAX function from the drop-down in ‘Summarize by.’

Double-click on cell J1 and replace “MAX of Running Total” with “Running Total.”

This method allows you to seamlessly add running totals to Pivot Tables when using fields exclusively under the ROWS section in Google Sheets.

Adding Running Totals in Pivot Tables Grouped By Rows and Columns

What about adding running totals in rows in the Pivot Table in Google Sheets?

In the above example, the cumulative sums are added in a column in the Pivot Table because we have grouped the data by the rows.

If we use fields under the ROWS and COLUMNS section of the Pivot Table editor, then running totals need to be added under multiple columns, and we read them row by row.

Let’s learn this with an example. Here, too, we have data in A1:D, where A, B, C, and D contain Date, Category, Sub-category, and Qty., respectively.

Consider the following Pivot Table editor settings.

I have added the fields ‘Category’ under ROWS, and ‘Sub Category’ and ‘Date’ under COLUMNS. Additionally, I’ve included the ‘Category’ field under the FILTER section to filter out blank rows in the range being evaluated.

Formula and Additional Settings

Sort the data in the order Category > Sub-Category > Date. This follows the fields under ROWS and COLUMNS. This is similar to our earlier settings.

1. Select A1:D13 and click on Data > Sort range > Advanced range sorting options.
2. Check “Data has header row.”
3. Sort by Category > Sub Category > Date.

Use the ‘Category’ and ‘Sub Category’ fields for grouping in the formula. As earlier, we don’t need to consider the last field in grouping; here, it is the “Date.’

Insert the following formula in cell E2:

=ARRAYFORMULA(MAP(
B2:B, C2:C, 
LAMBDA(b, c, IF(b="", ,
   SUMIFS(D2:D, B2:B, b, C2:C, c, ROW(A2:A), "<="&ROW(b))
))))

To add a running total to the Pivot Table, you need four more settings to finish.

1. Right-click on any date in the Pivot Table report and select Create pivot date group > Month (you can choose Year, Quarter, etc., depending on your requirement).
2. Open the Pivot Table editor and add the ‘Running Total’ field below the VALUES section.
3. Select Max under “Summarize by.”
4. Double-click on cell G1 and replace “MAX of Running Total” with “Running Total.”

Additional Notes

If you group by ROWS and COLUMNS, include only one field under the ROWS section in the Pivot Table. Adding another field will result in a drill-down in the Pivot Table report, which may not be useful as cumulative totals are arranged horizontally.

You may sometimes encounter the blank cell issue, as explained at the beginning of this tutorial.

Key Takeaways

Running totals provide valuable insights for performance tracking and comparative analysis. Integrating them into Pivot Tables in Google Sheets enhances their utility.

In this tutorial, we’ve covered how to incorporate running totals into Pivot Tables in Google Sheets. Here are the key takeaways:

1. Start by creating the desired Pivot Table.
2. Sort the source data according to the grouping order in the Pivot Table editor panel.
3. In a helper column, employ a formula to calculate cumulative totals based on the chosen grouping. The grouping should align with the sort order but exclude the last field.
4. Under the VALUES section, add the running total helper column field, and summarize by MAX. There’s no need to employ the Calculated Field.

That’s all!