In this tutorial, let me shed some light on how to get the total of the last column based on row-wise identical columns to its left in Google Sheets.
I want to total the last column of a table in a specific way. I mean, the total must be based on row-wise identical columns.
If we have very few numbers of columns (less than 5), then we can use a SUMIF formula for the same. But if the number of columns is more, then I have a different approach without SUMIF.
First of all, let me clarify what I meant to say by using the term row-wise identical columns in Google Sheets.
Here is a sample table.
I want to total the last column E if the columns of its left are identical.
Here we won’t consider whole columns like columns B, C, and D. Instead, we will consider row-wise columns. That means B3, C3, and D3 are the columns left of the last column, E3.
As per the above definition, the row-wise identical columns are in rows 3 and 6. If we total the last column as per this, we will get 7, i.e., a total of the cells E3 and E6.
Total Last Column if Columns of Its Left are Row-Wise Identical
The SUMIF Way
As I have mentioned, we can use SUMIF for the same in a table that contains a few columns. So for the example, we can use the above table in B2:E6.
We must first find (mark) the rows to include in the total that we can achieve in two ways – with or without using a helper column (an additional column or column range).
Helper Column
The AND function is involved in the helper column approach. Insert the below AND formula in cell F3 and drag it down.
=and(B3=B3,C3=B3,D3=B3)
The cells that contain TRUE are the rows to include in the total. Using SUMIF in this range (F3:F6), we can get the total.
Key the following formula in cell H3 which would return the total 7.
=sumif(F3:F6,true,E3:E6)The above SUMIF formula sums the last column if the columns of its left are row-wise identical.
The draw-back of the above Google Sheets formula is the helper column which contains a non-array AND.
Without Helper Column
We can replace the AND formulas in F3:F6 with the below array formula in F3, which uses the multiplication operator (asterisk), which is equal to AND.
=ArrayFormula((B3:B6=B3:B6)*(C3:C6=B3:B6)*(D3:D6=B3:B6))Related: How to Use IF, AND, OR in Array in Google Sheets.
It returns 1 or 0 instead of TRUE or FALSE. So in the above SUMIF, replace F3:F6 with the above formula and replace TRUE with 1.
=sumif(ArrayFormula((B3:B6=B3:B6)*(C3:C6=B3:B6)*(D3:D6=B3:B6)),1,E3:E6)Above is the SUMIF array formula to total the last column if columns to its left are row-wise identical.
A Dynamic Formula to Total Last Column if Columns of Its Left are Row-Wise Identical
Using the above SUMIF formula is not ideal if the number of columns in your table is large.
Because, in such case, we may refer to each column individually, that may cause accidental syntax error or makes the formula lengthier.
If you have the above concern, to address it, I have a dynamic formula to total the last column if the columns of its left are (row-wise) identical.
Logic Part
The logic, I am going to elaborate on the sample data provided above.
It’s like;
If B&C&D=B&B&B return E3:E6. Then total the returned values.
Didn’t get it? It’s like this.
We will combine the columns in the range dynamically. Then replicate the first column based on the number of total columns. Then we will compare both the returned columns and sum the matching rows.
Formula Part
We can use the QUERY header on the TRANSPOSED range and then TRANSPOSE the result to get B&C&D as below.
Formula # 1
=ArrayFormula(transpose(TRIM(query(transpose(B3:D),,9^9))))
To replicate the first column, we can use the following REPT formula.
Formula # 2
=ArrayFormula(TRIM(rept(B3:B&" ",Columns(B3:D))))
The COLUMNS in the formula returns the total number of columns in the range. So the REPT formula repeats the first column based on the total number of columns, here 3.
Let’s match the result and Sum.
Generic Formula
sum(if(formula_1_result=formula_2_result,E3:E))Here is the formula.
=ArrayFormula(sum(if(transpose(TRIM(query(transpose(B3:D),,9^9)))=TRIM(rept(B3:B&" ",columns(B3:D))),E3:E)))The above is the dynamic formula to total the last column if the columns of its left are identical.
That’s all. Thanks for the stay. Enjoy!
