In a sorted range in Google Doc Sheets, you can use my array formula to sum column B when the value in column A changes.
Of course, you can replace columns A and B with other columns of your’s choice. You can use distant columns too.
You may please jump to the first screenshot below to know what I mean.
I’ve two array formulas that sum a column when a change in value in another column occurs. What are they?
- A standard solution.
- A lambda-based solution.
I have a non-array formula already. That is very simple to understand and use – Add Total Based on Each Change in a Column Value in Google Sheets.
But I recommend the formulas below as it’s auto-expanding!
In the below example, my formula is in cell C2.
It returns the sum of cells when values change happen in another column.
It sums the cells in column B at each value change in column A.
Since it’s an array formula, it takes account of all the value changes in column A. I am adding new values and see the sum populating in cell C9.
The Regular Array Formula to Sum Column B When Value in Column A Changes
The following formula calculates the sum of cells until a value changes in another column. Just enter this formula in cell C2. It covers the entire columns A and B.
Master Formula 1:
=iferror(ArrayFormula(vlookup(row(A2:A),{if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))),if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B))},2,0)))How do we use two different columns other than A and B?
First of all, please take a look at the formula on the screenshot below and the two different color highlighting.
Green Highlighted: It’s the first column in our data. Any changes in the values in this column affect the total.
Orange Highlighted: The column to sum when the value changes in A2:A.
You can see that I have not highlighted the formula row(A2:A). Please note that that will remain the same irrespective of the columns used in the formula.
But if your column A range is A5:A and column B range is B5:B, you should change the formula row(A2:A) to row(A5:A).
Formula Explanation
Rather than going for a detailed explanation of the formula, I am just providing you with the formula logic. It’s not a must to follow.
Actually, Master Formula # 1 above is a combination formula that acts together as a Vlookup formula.
Syntax: VLOOKUP(search_key, range, index, [is_sorted])
Here are the Vlookup formula arguments used in Master Formula # 1.
search_key:
The formula, i.e., row(A2:A), is the search_key that returns the row numbers from 2, 3, 4, 5 … in sequential.
When testing this ROW formula, you must wrap it within the Array Formula.
=arrayformula(row(A2:A))
range:
The formulas underlined in Red and Green colors are the range in Vlookup.
It forms a two-column range because I have enclosed them within Curly Brackets in Master Formula # 1.
To make you understand what they return, I have entered them in cells F2 and H2, respectively.
Note: Here also, we must use the Array Formula function to make them work in independent use.
F2:
=ArrayFormula(if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))))This formula returns the row number of the value changes in column A.
H2:
=ArrayFormula(if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B)))This is a Sumif formula that groups column A and returns the sum.
Together both the above formulas form the range for Vlookup virtually as below.
index:
The index is the column number in the range.
I have used #2 as the index number since column 2 in the range (please see image # 5 above) contains the sum of each change in value.
Formula Logic
Let’s see the logic behind how to sum column B when values in column A change.
To explain, I am entering the above Vlookup search key and range in another tab (range in A:B and search_keys in D:D). Then a Vlookup in F2.
Please check column C in image # 4 above and the output in column F below.
If I wrap this formula with Iferror, I can hide the #N/A error.
This way, we can use an array formula to sum column B when values in column A change in Google Sheets.
The Lambda Solution to Sum Column B When Value Changes in Column A
Master Formula 2:
=ArrayFormula(ifna(lambda(x,y,byrow(x,lambda(r,if(row(r)=xmatch(r,x,0,-1)+1, sum(filter(y,x=r)),))))(A2:A,B2:B)))We can use the above formula as an alternative to our Master Formula # 1.
It’s somewhat easy to understand how this lambda formula sums column B when the value changes in column A.
It works like this.
The FILTER filters column B if the column A value is A2 (criterion). The SUM function totals it. That is the first column change value to place in cell C4 as per image # 4.
To find the row to place, we have used the XMATCH function, which returns the last row of the search_key (filter criterion) in A2.
Using IF, we place that total in that row.
We have used BYROW to repeat the above process with each criterion in FILTER (A2, A3, A4, …)
The role of the LAMBDA here is to shorten the formula.
Related Reading:
