A simpler approach to this At the beginning of column B, type =UNIQUE(A:A) Then in column C, use =COUNTIF(A:A, B1) and copy them in all row column C. Edit: If that doesn’t work for you, try using semicolon instead of comma: =COUNTIF(A:A; B1)
This is done using the setFormula for a selected cell. Below is an example of how to do this. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(“B5”); cell.setFormula(“=SUM(B3:B4)”); You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = … Read more
AND doesn’t work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions. I.e. it checks if “”>”” which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down. You can use multiplication of … Read more
To get an actual Date value which you can format using normal number formatting… =DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8)) eg. A B 1 2016-02-22T05:03:21Z 2/22/16 5:03:21 AM Assumes timestamps are in UTC Ignores milliseconds (though you could add easily enough) The DATEVALUE() function turns a formatted date string into a value, and TIMEVALUE() does the same for … Read more
Just use: ={sheet1!a:a; sheet2!a:a}
Similar answer to caligari’s answer, but we can tidy it up by just specifying the full column range: =INDEX(G2:G, COUNT(G2:G))
I just used =COUNTIF(Range, “<>”) and it counted non-empty cells for me.
It looks like some of the other answers have become outdated, but for me this worked: Click on the cell with the text/formula to copy Shift+Click on the last cell to copy to Ctrl + Enter (Cmd + Enter on Mac.) (Note that this replaces text if the destination cells aren’t empty)
You can pass “<>” (including the quotes) as the parameter for criteria. This basically says, as long as its not empty/blank, count it. I believe this is what you want. =COUNTIF(A1:A10, “<>”) Otherwise you can use CountA as Scott suggests
There may be a more eloquent way, but this is the way I came up with: The function to find the last populated cell in a column is: =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ) So if you combine … Read more