Let’s say the In Time cells are in Column A, and Out Time cells are in Column B, and you want Time Spent to be in Column C. Put this formula in cell C2 (assuming A1, B1, and C1 contain headers, not data): =ARRAYFORMULA(B2:B – A2:A) The ARRAYFORMULA function instructs the spreadsheet to iterate the … Read more
You may replace all non-digit characters using the \D+ regex and an empty string replacement with =REGEXREPLACE(A11,”\D+”, “”) or with casting it to a number: =VALUE(REGEXREPLACE(A11,”\D+”, “”))
One option: =COUNTIF(B:B; “Mammal”) + COUNTIF(B:B; “Bird”) According to the documentation: Notes COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA. COUNTIFS: This function is only available in the new Google Sheets. Example: =DCOUNTA(B:B; 2; {“Type”; “Mammal”; “Bird”})
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)
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
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))
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)