New answers tagged

0

You can use ARRAYFORMULA for this. Here is an example of what you'd use =ArrayFormula(Sheet1!A:A) where Sheet1 is the name of the sheet you're pulling from and A is the column in that sheet you are trying to get.


0

Change =COUNTIF(A1:A5, "=ISDATE()") to =COUNTIF(B1:B5, TRUE) This will count how many ISDATE function calls in column B have evaluated to TRUE. Instead of what you were doing which was counting how many times the string =ISDATE() appears in the first 5 cells of column A.


-2

Column B =ISDATE(A1) B6 =COUNTIF(B1:B5,TRUE)


-2

Updated Saulo's script /** * Convert a cell reference from A1Notation to 0-based indices (for arrays) * or 1-based indices (for Spreadsheet Service methods). * * @param {String} cellA1 Cell reference to be converted. * @param {Number} index (optional, default 0) Indicate 0 or 1 indexing * * @return {object} {row,col}, both 0-...


0

You could do something like this: =IFERROR(ARRAY_CONSTRAIN(FILTER( QUERY(A:A, "SELECT A WHERE A CONTAINS '"&$B1&"'"), LEN(QUERY(A:A, "SELECT A WHERE A CONTAINS '"&$B1&"'")) = MAX(LEN(QUERY(A:A, "SELECT A WHERE A CONTAINS '"&$B1&"'"))) ), 1, 1), B1) So we QUERY the A ...


0

The second parameter to importrange() is just a text string, so you can concatenate the sheet name and the range reference like this: =importrange("1u0TjjekVHHb6shYiSWoo0W1ygxuSu2tVvLstnEE1NFo"; A1 & "!I5") One would usually get a range of cells with an importrange() formula rather than getting just a single cell. You may want to ...


0

Answered at the Sheets forum: =arrayformula( { "Duration"; array_constrain( iferror( 1 / ( sign(B3:B) / (C3:C + B3:B - (C2:C + B2:B)) ) ), rows(B2:B), 1 ) } ) =arrayformula( { "Dur. dateserial"; to_pure_number(D2:D) } ) =query( 'Calculate durations'!A1:E, "select C, ...


-1

This article says that can be achieved by writing an app script that then takes the importxml xpath as array driving down the column row by row producing the results in the desired column. https://stackoverflow.com/questions/33147567/how-to-place-importxml-result-to-a-google-script-array


0

Your data table looks nice and pretty — i.e., it is like a report. The best practice is to keep your data row-oriented and simple, and create reports separately — do not try to make your data look like a report. You may want to use a row-oriented data layout, like this: Name             Subject     Quarter   Score Marie Curie       Chemistry   Q1       100 ...


0

You can put the names of the months/sheets in a column, let's say "jan" is in A2, you can use a formula in B2 that uses the value in A2, indirect and concatenate to find the sum of one sheet: =SUM(indirect(concatenate(A2, "!$D2:$Z2"))) Now you can put feb, mar etcetera in the columns below, and drag/copy the formula to get the results ...


0

Ok, now this looks good!!! (yet complicated) create 2 named ranges: 1: lst_hds (range(A1:D1) to contain the game names, 2: lst_db (range(A3:D5)) to contain the players. still in B10 the name you search for... now copy-paste: =arrayformula( if(mid( text(sum( ( if( lst_db=$B$10, 10^(column(lst_db)-1),0) ) ),"0000") ,columns(lst_hds)+1-COLUMN(...


1

This behavior is a side effect of the memoization used by Google Sheets to cache the return values of custom functions. Its purpose is to improve the performance of custom functions. As long as the custom function's parameters remain the same, it will return the cached result without recalculating it. The standard recipe to make the spreadsheet recalculate ...


2

If you need to process only one cell, you can use this in B2 (supposing that cell is A2): =ArrayFormula(SUM(IF(IFERROR(SEARCH(CHAR(64+SEQUENCE(1,26)),A2))>0,1))) If you need to process an entire range of cells (say, A2:A), you can use this in B2 of a previously empty B2:B range: =ArrayFormula(IF(A2:A="",,MMULT(IF(ISNUMBER(SEARCH(CHAR(64+SEQUENCE(...


2

Please use the following formula =COUNTUNIQUE(SPLIT(REGEXREPLACE(REGEXREPLACE(LOWER(S1),"[\W|\d]",""),"(.)","$1 ")," ")) Functions used: COUNTUNIQUE SPLIT REGEXREPLACE LOWER


0

You want to count items by group/category for each row of a list. There are likely to be many ways to solve your question. The following should be considered as one alternative: This answer combines countif with vlookup in an arrayformula. Headers In cell N1, O1, P1 and Q1, enter: "Unit 1", Unit 2", "Unit 3" and "Unit 4" ...


1

The two cleanest ways to implement this: Use {} around the query range so you can use columns instead of letters. E.g.: =Query({A:B},"Select Col"&Match("Header Name",1:1,0)) Where A:B is where your query data lives and 1:1 is the where the headers live. Name the range you want to pull from. You can reference the named range within ...


0

Instead of {'01 July - 12 July 2021'!G10:G22,'June 2021'!G10:G22,'May 2021'!G11:G19,'April 2021'!G10:G20} use '01 July - 12 July 2021'!G10:G22; 'June 2021'!G10:G22; 'May 2021'!G11:G19;'April 2021'!G10:G20} The fist array returns an error because the ranges hasn't the same number of rows (two has 22, one has 18 and other has 20). The second array will not ...


0

here i attach a sheet link which i got share basis i need to this sheet formula to edit and make new my self to add my stock and remove other with this depend on market behave so i need your help if you give this sheet col-um O & AG formula https://docs.google.com/spreadsheets/d/1gnMfp9wgAkY2t1i4peA6iwKY5zDOQmKKu6i_sr42xNY/edit#gid=2117517045


0

I was having this issue also, where my query results were not numeric. It was because some cells had text instead of a number. As you mentioned, one solution is to create a new column which only contains the numeric values. Otherwise you can do it "on the fly" using the =FILTER() function. See example below: =QUERY(FILTER(A1:D10, ISNUMBER(D1:D10)), ...


1

Use filter views to let view-only users sort the sheet. This feature lets multiple simultaneous users sort and filter the sheet without disturbing each other. For additional ease of use, insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views ...


0

In case you cannot find a solution using formulas I will give a solution using the following code: function onEdit(e) { const as = e.source.getActiveSheet(); const row = e.range.getRow(); const col = e.range.getColumn(); const lc = as.getLastColumn(); const lr = as.getLastRow(); const rg = as.getRange(2, 1, lr, lc); let asc = true; let val =...


0

I read the solution offered by doubleunary. It does have one flaw, which is that the formula as written will incorrectly convert integer-only numbers (e.g., 4, 5, 6, etc.). The following modification will work in all cases: =regexextract(trim(F4), "^\d+") + iferror(regexextract(trim(F4), ".(\d+)$") / 12) In order to keep the structure of ...


0

Let us assume you have the value 12.06 in cell F4, and this formula in cell G4: =regexextract(trim(F4), "^\d+") + regexextract(trim(F4), "\d+$") / 12 The easiest way to get back to the value in cell F4 is as follows: =F4 ...while here is the unnecessarily convoluted way to back to the value in cell F4, using the result in cell G4 for no ...


0

You want a formula to calculate cost of items taking into account two attributes (Tier and Size). Consider this formula: =if(countif('New Trees/Plants Installation'!$A$11:$A,$A3),if($B3="SMALL: 15 Gallon/under 6 feet tall/under 1 inch caliper",$C3*'New Trees/Plants Installation'!$G$2,if($B3="MEDIUM: 30 Gallon/6 - 10 ft. tall/1 - 1.9 inch ...


0

Try this: =regexextract(trim(F4), "^\d+") + regexextract(trim(F4), "\d+$") / 12


0

The "Formula parse error" usually is due to a syntax error. In this case, the formula included in the question(=IFS((=LEFTB(A:A;3))=Sheet2!A:A ; Sheet2!B:B)) has a syntax error: The equal sign could be used as an unary operator only when it's the first character in a cell value to indicate the cell content is a formula, in other words, (=LEFTB(A:A;...


Top 50 recent answers are included