CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulagoogle-query-languagetextjoin
Jey Jay
Jey Jay

Reputation: 113

Google Sheets count unique for all columns of a spreadsheet

I have a spreadsheet with names and months. I'd like to do, for each month, the Sum for each name.

In O1 my formula is:

=query(A:M,"select A, sum(B), sum (C), sum(D) group by A offset 1")

The formula does the trick (here for the first 3 months) but I'm adding months after Month 12 so rapidly I'll have 50 months to count for.

Is there a way to add all the months instead of typing each time the column letter?

Second question, is there a way to change the name of the column label, instead of having sum Month 1, sum Month 2 etc

Names&Months

Thanks!

Upvotes: 1

Views: 98

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(QUERY(QUERY({A2:1000}, 
 "select Col1,"&TEXTJOIN(",", 1, 
 "sum(Col"&SEQUENCE(COUNTA(B1:1), 1, 2)&")")&"
  where Col1 is not null 
  group by Col1"), 
 "offset 1", 0))

enter image description here

Upvotes: 4

Related Questions