CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Teed Ferguson
Teed Ferguson

Reputation: 317

Can I add a floating column to a Sheets query?

I have a Sheet like this [minimal] example. The "Data" tab is a running list of points different groups have scored. This is constantly updating. The "Results" tabs gets a list of all the unique group names from the Data tab and add them as a row in Column A. So, if an entry for "Group5" is entered on the Data tab, a row with that name will appear on the Results tab. Similar for the columns on the results tab. Each unique action on the Data tab becomes a column on the results tab. I would like to add a "Total" column to the last column on the Results tab. The problem is, if a new "action" category appeared on the data tab, it would take up the last column and the Total column would need to shift over one. Is there a way to get the "Total" column to float so that it is always on the end without conflicting with the query formula?

Upvotes: 0

Views: 208

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

In B1 cell put below formula-

={TRANSPOSE(UNIQUE(QUERY(Data!C2:C, "SELECT C where C is not null",0))),"Total"}

In B2 cell put below formula then drag down and right as needed.

=IF(OR($A2="",B$1=""),"",IF(B$1="Total",SUMIFS(Data!$D$2:$D,Data!$B$2:$B,$A2),
SUMIFS(Data!$D$2:$D,Data!$C$2:$C,B$1,Data!$B$2:$B,$A2)))

enter image description here

Upvotes: 1

Related Questions