In Google Sheets How to Total on Each Changes in a Column Value

How to Total on Each Changes in a Column Value

As of now, there is no option in Google Sheets similar to Excel’s Group or Subtotal command. This command enables users to add total on each changes in a column value.

In Google Sheets the possibly best way to achieve similar result is by using Pivot Table. If you just want the group wise total, of course, you can also use QUERY function or even SUMIFS. But all these functions and command populate the data in a separate range not in the existing range.

A command similar to subtotal in Excel, as a side note subtotal function is different, is what I am badly missing in Google Sheets. In a limited way, we can overcome this with a custom formula.

How to Total on Each Changes in a Column Value in Google Sheets

Sample Data:

Example to Total on Each Changes in a Column Value

In this Google Sheets example, there are four fruit items in Column A and the column is sorted. You can see the total in Column D on each change. To get such total on each changes in a Column value, I’ve applied a custom Google Sheets formula in D2 and then copied it to down.



I know I should explain this formula then only you can use it on any column in your original data.

Formula Explanation Part:

As I told you, it’s not an array formula. You should copy it to down. So for explanation purpose I’m taking you to Cell D10 formula. See the screenshot.

subtotal grouping in google sheets

The formula in D10 tests the value in A11 whether it’s equal to the value in A10. If A11 value is different, in D10 the IF logical function would execute the SUM function else return blank. The SUM function sums Column C up to C10 and then minus any value in column D up to D9. See the above screenshot and you can grasp it. That’s all.

The only drawback of this formula is, it’s not an array formula to expand itself.

Additional Info.: If you just want the subtotal or grouped total, the below Query formula is enough. It populates the summary in a new range.

=query(A1:C,“select A, sum(C) where A<>” group by A”, 1)

Hope you enjoy.


Please enter your comment!
Please enter your name here