The 2024 Developer Survey results are live! See the results

# Tag Info

Accepted

### How can I create a unique list of data across multiple rows AND columns?

=UNIQUE({A:A;B:B;C:C}) =ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}), "select Col1 where Col1 is not null")))
• 26.8k

### How can I create a unique list of data across multiple rows AND columns?

=unique(flatten(A1:Z)) I think this will work and it worked for me.
• 131

### How to Fill a Column with Sequential Dates in Google Sheets

The next level would be to use ARRAYFORMULA: =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE("2018-4-30"))), "dd/mm/yyyy"))
• 26.8k
Accepted

### How to use custom function with arrayformula?

In order to use a custom function with array formula, it should be able to return to get as input and return as result array of values. The custom function provided as example has no input parameter ...
Accepted

### Google Sheets: Is there kind of map function?

Alternatives: Use ArrayFormula / MMULT functions Limitations: Some formulas should be "redesigned in order to be used with ArrayFormula / MMULT or other similar built-in functions. Related Q&...

Accepted

### Summing top three values in a column from a Google sheet

Formula =ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0))) Explanation LARGE returns the nth greater element. IFERROR if the first argument returns an error, returns the second argument. {1,2,3}...

### LEFT JOIN two and more tables in Google Sheets

one-cell solution (paste in D1): ={"material", "required", "total"; ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )), ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), )...
• 26.8k
Accepted

### Convert Text String into Date/Time Value

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")), TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")} with timezone in account: ={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), ...
• 26.8k

### Cumulative Sum without Script

The accepted answers are far too complicated for running tallies. A much easier answer is: =SUM(A\$1:A1) Substitute the A for the column you want. Substitute 1 for the row you want. Click the small ...

### Cumulative Sum without Script

While the accepted answer is on spot it has a major disadvantage due to the fact that the last parameter of SUMIF needs to be always a "valid range" so when it comes to some more advanced running ...
• 26.8k

### How to use AVERAGE inside ARRAYFORMULA in Google Sheets

Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA. One ...

### Escape single quote in cell content in a QUERY

Plain simple quote "inversion": =query('Book list'!A:F,"select B,C,D,E where F=""free"" and D="""&E1&"""")
• 61
Accepted

### Rearrange Name Format in Google Sheets using one formula only

Delete everything from B:B (including the header) and try this formula in B1: =ArrayFormula({"Reformatted Name";IF(A2:A="",,TRIM(REGEXREPLACE(REGEXREPLACE(A2:A,"Jr\$|Jr.\$|Sr\$|...
• 5,078

### LEFT JOIN two and more tables in Google Sheets

Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy. Supose you have all in A1:C5 and ...
Accepted

### Pie chart with sub-slices per category in Google Sheets?

your dataset of A1:C is not in "good" shape for creating a chart, so first, you will need to re-shape it by adding few formulas in auxiliary columns which you can hide when done in cell E1 paste this ...
• 26.8k
Accepted

### In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

...understanding the ARRAY_LITERAL ERROR: until both queries/filters/formulas output something then all is good: however if one of those queries/filters/formulas doesn't have anything to output it ...
• 26.8k
Accepted

### Print incremental numbers in Google Sheets

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =...
• 12.9k

### ISBLANK function returning unexpected results

To check if ALL cells in range A2:C2 are blank =AND(ARRAYFORMULA(ISBLANK(A2:C2))) To check if ANY cell in range A2:C2 is blank =OR(ARRAYFORMULA(ISBLANK(A2:C2)))
• 153
Accepted

### Is there a way to return multiple columns with XLOOKUP within an ARRAYFORMULA?

Use map(), like this: =map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G))) See map() and lambda().
• 15.1k

### How to Fill a Column with Sequential Dates in Google Sheets

Logic: In first column we can put current date using =Today() this will give current date. In order to get next date we must increase the date by 1. To get next date we can add =Today()+1 If you ...