0

I need to sum all of the columns between two given columns in a query.

So, I need to figure out how to create a plus-sign-seperated list of all columns letters, between two given columns letters.

The formula should take two inputs, the starting column (e.g. 'P'), and the ending column (e.g. 'AC'), and output a plus-sign-seperated string of column letters ('P+Q+R+S+T+U+V+W+X+Y+Z+AA+AB+AC')

Input 1: 'P'

Input 2: 'AC'

Expected output: 'P+Q+R+S+T+U+V+W+X+Y+Z+AA+AB+AC'

Example of complete expected query: '=QUERY(data, SELECT "P+Q+R+S+T+U+V+W+X+Y+Z+AA+AB+AC")'

I have tried to tweak a formula I found via a related post without any success.

enter image description here

WIP sheet: https://docs.google.com/spreadsheets/d/1ewNMdx2BXI8pVnZehX4kHrMVAvWNbbjeIW-hrXUIPeA/edit?usp=sharing

Related posts: google sheets, Make letters separated by comma given number of following columns. Not using app script, just formulas please

Any help would be much appreciated!

UPDATE

How to create a plus-sign-seperated list of column letters, between two given column letters?

Formula: =LAMBDA(letters,TEXTJOIN("+",1,FILTER(letters,SEQUENCE(COUNTA(letters))>=MATCH(B3,letters,0),SEQUENCE(COUNTA(letters))<=MATCH(B4,letters,0))))

Note

  • In the above mentioned formula, B3 is input1 (i.e. the start column), and B4 is input2 (i.e. the end column).
  • The sheet where you place the formula must include the columns you specify in input1 and input2.

Latest demo

https://docs.google.com/spreadsheets/d/1Kl29_0o8mKfeD--vkZHKU3EIf9hl-ts93gX8Ou08EwY/edit?usp=sharing

NestoJosa
  • 35
  • 1
  • 6
  • What's your actual final desired result? You may not need to quote all letters – Martín Feb 24 '23 at 13:01
  • Hi Martin, first and foremost thanks for you time! Now, I have update my "WIP sheet" with some example data that hopefully shows what I am trying to accomplish. In Sheet1 we have projects and the hours that a member has used on a project. What I need to find out is the total number of hours between two dates - see Sheet2. I think that I am on the right track, but it would be great to see any alternative solutions. Thanks beforehand! – NestoJosa Feb 24 '23 at 15:00

2 Answers2

2

There are possibly other ways of doing what you expect without the concatenation of letters. Here you have a formula that does what you expect adapting our example:

=LAMBDA(letters,TEXTJOIN("+",1,FILTER(letters,SEQUENCE(COUNTA(letters))>=MATCH(A1,letters,0),SEQUENCE(COUNTA(letters))<=MATCH(B1,letters,0))))(ArrayFormula(REGEXREPLACE(ADDRESS(1,SEQUENCE(COLUMNS(1:1)),4),"\d+","")))

enter image description here

I was asking for your final expected result, because you could use other ways. For example if you need the sums per row:

=QUERY({Sheet2!A:D,BYROW(INDIRECT("Sheet2!"&A1&":"&B1),LAMBDA(e,SUM(e)))},"Select * Where Col1 is not null",1)

This would be a QUERY where you previously sum with indirect the columns you desire row by row and then apply the QUERY. Obviously you'd be able to add all the conditions you need too!

UPDATE

Here you have a way of doing it with dates:

=QUERY({Sheet1!A:C,BYROW(FILTER(Indirect("Sheet1!H1:"&ROWS(Sheet1!H:H)),Sheet1!H1:1>=MAX(0,B5),Sheet1!H1:1<=MIN(B6,999^99)),LAMBDA(r,SUM(r)))},"Select *"&IF(B7="",""," where Col1 = '"&B7&"'")&"label Col4 'Hours'", 1)

enter image description here

  • Indirect("Sheet1!H1:"&ROWS(Sheet1!H:H)) allows you to go from the H1 to the last column. It's like H1:1000, or H1:2345 depending on your current amount of rows in Sheet1
  • With FILTER I see when the dates are greater or smaller than the two variables. I added the MIN and MAX values so you can leave the values empty in case you don't want to establish one of those values
  • With BYROW I find the sums of that filtered range of dates "row by row"
  • All inside QUERY leaves a four column range, being the fourth the amount of hours of each row with the filtered dates. I've added the option to filter or not by the name in B7 (you can leave it empty if you want)

Let me know! It's in Copy of Sheet2

Martín
  • 7,849
  • 2
  • 3
  • 13
  • Hello again Martin, it seems like your solution does not work if first input is "A" and second is "BB" (or any other column that is NOT currently present in the sheet). My best guess is that as soon as one inputs column-letter(s) that are NOT in the same sheet as the formula, it breaks. So, is there some way to make the formula independent of the columns present in the sheet? – NestoJosa Feb 24 '23 at 15:10
  • See the update! I've done a different method with dhates. The limitation in your first formula was in the amount of columns that didn't match from both sheets. I guess that something like this would do it: `=LAMBDA(letters,TEXTJOIN("+",1,FILTER(letters,SEQUENCE(COUNTA(letters))>=MATCH(A1,letters,0),SEQUENCE(COUNTA(letters))<=MATCH(B1,letters,0))))(ArrayFormula(REGEXREPLACE(ADDRESS(1,SEQUENCE(COLUMNS(Sheet1!1:1)),4),"\d+","")))` But I think the new option I've put is more versatile since you don't have to calculate the columns and just put the dates – Martín Feb 24 '23 at 15:51
  • Thanks for the update! My original data spreadsheet does not contain any actual dates, instead the days are numbered. So, the solution with dates will not work. But I did manage to solve the OG problem thanks to your formula - many thanks! The only thing left to solve, is that the formula breaks when there is a cell (in the columns to be summed) with no value. I am trying to solve it with the arrayformula function but not sure how to. Do you have any ideas? Here is the latest demo-sheet: https://docs.google.com/spreadsheets/d/1Kl29_0o8mKfeD--vkZHKU3EIf9hl-ts93gX8Ou08EwY/edit?usp=sharing – NestoJosa Feb 25 '23 at 14:34
  • You could try to wrap the range in ARRAYFORMULA and If: =ARRAYFORMULA(IF(range="",0,range)) . If the value is empty it will be converted into 0 – Martín Feb 25 '23 at 14:57
  • I tried that but it did not work - I'm getting " Error - Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: A". The OG formula: "=QUERY(Data!A:X, query, 1)". The formula I tried: "=QUERY(ARRAYFORMULA(IF(Data!A:X="",0,Data!A:X)), query, 1)". Do you have any idea what I am doing wrong? Thanks for your patience and help! Link to demo-sheet: https://docs.google.com/spreadsheets/d/1Kl29_0o8mKfeD--vkZHKU3EIf9hl-ts93gX8Ou08EwY/edit?usp=sharing – NestoJosa Feb 25 '23 at 15:23
  • 1
    With those requirements you should change your column letters to column numbers. With the ARRAY replacement with 0 it's not anymore the original range, but a new one with no column names. Because of that you'll need to do the same process but with Col1, Col2, etc. I've created a copy of Dev and WIP01 for you to test it – Martín Feb 25 '23 at 15:43
  • Eventually I figured that the error was due to the usage of columns letters and not numbers which the arrayformula expects, but I was unable to refactor the formula, so, needless to say you have helped me tremendously and for that I salute you sir! :) I have tested it out and it seems to work as expected :D Can't thank you enough - Have a great weekend! – NestoJosa Feb 25 '23 at 19:40
  • I'm glad it finally worked! – Martín Feb 25 '23 at 21:02
0

This Google Apps Script asks you for Input 1 and Input 2 and returns all column letters between.

Input 1 = C

Input 2 = J

Output = C+D+E+F+G+H+I+J

You need to specify the cell to set the value with this output.

function getColumnLetters() {
        var sheet = SpreadsheetApp.getActiveSheet();
        var startColLetter = Browser.inputBox("Letter of the starting column:");
        var endColLetter = Browser.inputBox("Letter of the ending column:");

        var startColIndex = sheet.getRange(startColLetter + "1")
                                 .getColumn();
        var endColIndex = sheet.getRange(endColLetter + "1")
                               .getColumn();

        var columnCount = endColIndex - startColIndex + 1;
        var columnLetters = [];
        for (var i = 0; i < columnCount; i++) {
          var currentColIndex = startColIndex + i;
          var currentColLetter = sheet.getRange(1, currentColIndex)
                                      .getA1Notation()
                                      .match(/[A-Z]+/)[0];
          columnLetters.push(currentColLetter);
        }

        var output = columnLetters.join("+")
        // Here is your output
        Logger.log(output)
        // This will show up in the message box
        Browser.msgBox("Output: " + output);
      }
Greg
  • 11
  • 3