Search code examples
excelgoogle-sheetsexcel-formulagoogle-sheets-formula

Excel to Google Sheets formula conversion

I have the following formula that works out the value of all the rows between B2 and the bottom of the column (reason being that the column is constantly extending). This is how it looks in Excel:

=SUM(B2:INDEX(B:B,MAX((B:B<>"")*(ROW(B:B)))))

and I would like to convert it to Google Spreadsheet format. Does anybody know if this is possible (i.e. the fact that the bottom row is 'unknown' - not formula conversion in general)? Copying and pasting verbatim does not work (kind of expected!)

Solution

  • Google Spreadsheet has a simple syntax for the range from a particular starting cell to the last cell in the column. For the range starting at B2, the syntax is B2:B. If you want the sum of the cells in the range, then, you can simply do:

    =sum( b2:b )

    Similarly, if you want a rowwise range, the analogous syntax is B2:2 and you could sum up the partial row with:

    =sum( b2:2 )