7
votes
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}...
4
votes
Accepted
Google Sheets - Group/Aggregate data
You can remove sizes from names with regexextract() in cell F1 like this:
=arrayformula( iferror( regexextract(A1:A, "(.+?)-[\dA-Z]"), A1:A ) )
Then query the data:
=query(A1:F, "select ...
- 15.7k
3
votes
Accepted
How to use a query to find a row of same name but last entry, then copy a value next to it
Please use the following
(Adjust ranges to your needs)
=SORTN(QUERY({INDEX(ROW(D2:D)),D2:E},
"select Col2, Col3
where Col2 is not null
...
- 13.1k
3
votes
Accepted
How to convert repeating rows into columns in Google Sheets
You can use the following query formula:
={{"Name";QUERY({B1:B},"where Col1 is not null and not Col1 contains '@'")},
{"Email";QUERY({B1:B},"where Col1 contains '@'")}}
Functions used:
QUERY
- 13.1k
3
votes
Summing top three values in a column from a Google sheet
You probably want to look at LARGE().
Returns the nth largest element from a data set, where n is user-defined.
The simplest, but by no means most elegant, solution I have is:
=if(iserror(large({...
- 52.9k
2
votes
Filter in Filter function Google sheets
The "elegant" way to use FILTER to filter A2:F where B2:B (Power) =I3 and D2:D (Manufacturer) =I2 is
=FILTER(A2:F,B2:B=I4,D2:D=I3)
The above works because filter allow multiple criteria arguments.
...
2
votes
Accepted
Create list of cell addresses contained in a range
This one is fairly strait forward, please see the code below:
=textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4)))
You can change the last 4 to a 1 if you want absolute references.
- 867
2
votes
=IMPORTHTML forces content to date format
There are likely to be many solutions to your question. Even in this answer, I would expect that the structure could be simplified - possiblky along the lines of the answer by Rubén in Trying to use ...
- 5,841
2
votes
Accepted
How do you perform a bulk find & replace using a list of search phrases and their replacements?
You could try using a separate table (Sheet2) to do the replacing. Column A is the same as here (Sheet1), columns Sheet2!B:Z (or how many are maximally needed) are used to SPLIT the terms in Sheet1!B ...
- 166
2
votes
How to have a query wrapped in an arrayformula auto-populate many rows
Place this formula in the top cell (i.e., header cell) of any otherwise empty column:
=ArrayFormula({"Header";IF(B2:B="",,COUNTIF(A2:A,B2:B))})
QUERY is not necessary, as you can ...
- 5,123
2
votes
=IMPORTHTML forces content to date format
@John Tokish, this should do the trick:
=ArrayFormula(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings","table",1)),"200|20.+...
- 5,123
2
votes
Accepted
Regexextract to get a text in the middle of string
Using a capturing group, we can match a pattern then choose which part of the pattern we want to return.
=ArrayFormula(REGEXEXTRACT(A2:A4,"^\d\s+(\w\d+\s+-\s+[A-z 0-9]+)"))
Notes:
I'm ...
- 1,100
2
votes
Accepted
Parsing the result of an array formula inline?
Figured it out. Inline, it can look like this:
=INDEX(GOOGLEFINANCE(B2, "high", DATE(2022,7,26)), 2, 2)
The "2, 2" part means "2'nd row, 2'nd column" and that's how I ...
- 121
2
votes
How to QUERY - group SUMed results?
Maybe you can create a two-columns range with curly brackets, by stacking those two columns: {AG2:AH;AI2:AJ}
=QUERY({AG2:AH;AI2:AJ}; "select Col1, sum(Col2) where Col1 is not null group by Col1 ...
- 1,426
1
vote
=IMPORTHTML forces content to date format
Here is a solution using a single formula
=ArrayFormula(
REGEXREPLACE(SPLIT(TRANSPOSE(SPLIT(JOIN("",TRANSPOSE(QUERY(TRANSPOSE(
{IMPORTXML(A1,"//tr/*"),TRANSPOSE(SPLIT(REPT(REPT(&...
1
vote
Accepted
Split text in Google Sheets with a delimiter which contains spaces
There are many ways to achieve the result you are looking. Here is one of the oldies, using RIGHT, LEN and FIND
=ArrayFormula(RIGHT(A:A,LEN(A:A) - FIND(" on ",A:A) - 3)
Another way, in this ...
1
vote
Accepted
Sum column value if any of several criteria is met in the adjacent column
Please note the comments made directly to your original post, noting that you have spelling errors in both the formula and the Col-A data.
However, assuming that your spelling were correct in Column A,...
- 5,123
1
vote
Accepted
Count instances of character in a column
EDIT (based on your comment)
No mixed characters, it will always be a single character or a repeat of a single character
For a draggable formula use
=SUM(INDEX(IFERROR(SEARCH(B10,$B$2:$E$7)*LEN($B$2:...
- 13.1k
1
vote
Accepted
Problem with results of filter function from tabs that have no data yet
To avoid the issue with the FILTER function, use a query instead.
=QUERY({January!A2:O;
February!A2:O;
March!A2:O}, "where Col1 is not null",0)
- 13.1k
1
vote
Accepted
Sheets ArrayFormula with SUMIF
I added a new sheet ("Erik Help") with the following array formula in D2:
=ArrayFormula(IF(B2:B="",,SUMIF(IF(ROW(B2:B),ROW(B2:B)),">="&ROW(B2:B),C2:C)))
The ...
- 5,123
1
vote
Accepted
Using query function with ' where Col1 or Col2 is not null'?
EDIT
(following OP's comment)
So, how to say if either Col1 or Col2 is not null ?
" where Col1 is not null and Col2 is not null "
Original answer
Depending on your locale, the formula ...
- 13.1k
1
vote
Use array as function parameters
Sheets' addition of the LET function enables storing intermediary calculation and addresses your need to limit repetition.
LET
QUERY(...) is a placeholder for your other formula.
=LET(q,QUERY(...), ln,...
- 7,482
1
vote
Use array as function parameters
Your split string is SPLIT("example.com,My Site", ","). The url is the first element, and the label is the second element.
Use Index to retrieve the separate elements of the split ...
- 5,841
1
vote
How to convert repeating rows into columns in Google Sheets
In C1 enter:
=index(B:B,2*row()-1)
and copy downward. In D1 enter:
=index(B:B,2*row())
and copy downward:
If you need to enter the formulas below row#1, replace:
row()
with:
rows($1:1)
- 191
1
vote
Accepted
Search a Google Spreadsheet and return results that are 'clickable' that takes you to the sheet and row
Instead of
=QUERY(Data1!A2:C, Data2!A2:C, Data3!A2:C "SELECT * WHERE A "&E3&" '"&F3&"'")
try
=QUERY({Data1!A2:C; Data2!A2:C; Data3!A2:C}, "SELECT * WHERE Col1 "&E3&" '"&...
1
vote
I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name
Try:
=SORT(UNIQUE(A3:B), 1, TRUE)
- 5,841
1
vote
Use a filter on an array of values
This is what is shown in the linked spreadsheet
Please note that each column includes a dropdown button. That is the way that Filter/Filter views work on Google Sheets.
If you want that the filter ...
1
vote
Accepted
Is there a way to generate many rows and columns from a single value?
In Example there is formula
={TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(transpose(E1:E8)&",", $A$2))), ",")),
TRANSPOSE(SPLIT(REPT(JOIN(",",TRANSPOSE(SEQUENCE(A2,1,B2,1)))&",",COUNTA(E1:E8)),...
- 781
1
vote
Accepted
Add single value as constant line to diagram
The only option in charts is to use one of the many trendline options.
Since your column is just a single arbitrary value and you want to plot it the best thing you can do is put it all in one ...
- 867
1
vote
Converting multiple columns to list
The solution was that I didn't need to unpivot, just reorder.
=query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null order by Col1"...
- 17
Only top scored, non community-wiki answers of a minimum length are eligible