New answers tagged google-sheets
0
votes
Accepted
How can I get VLOOKUP to return a response that is in one of two adjacent columns?
BYROW
You can use VLOOKUP to return a two-column array then use JOIN in BYROW to combine them into one.
={"Project Team Owner";
BYROW(
INDEX(
IFNA(
VLOOKUP(A2:A, 'Form ...
- 7,080
0
votes
Is it possible to add Dynamic subtotal in a google sheet without app script?
You are making this complicated by mixing counts and data (serial numbers) in the same column. Presumably there could be additional months for an assignee and it isn't clear if that would be an ...
- 7,080
1
vote
Which division is correct? Google Sheets or Calculator?
Everything is "correct" insofar as neither the calculator nor Sheets is making a mistake. Also, both sheets and the calculator can return either result if you ask them.
You are performing ...
- 7,080
1
vote
Accepted
Which division is correct? Google Sheets or Calculator?
Google Sheets calculates =218 / 294 to 0.741496598639456 which is quite precisely the same result as the calculator screenshot shows. So the explanation for the different result that is shown in the ...
- 15.3k
1
vote
Finding text with strikethrough and delete its entire row
Use Array.some(), like this:
function deleteRowsWithStrikeThroughInAnyCell() {
const ss = SpreadsheetApp.getActive();
let numRowsDeleted = 0;
ss.getActiveSheet().getDataRange().getFontLines()....
- 15.3k
1
vote
Conditionally format specific values within comma-separated values
Not without using a script-based solution, however, there may not be the need.
Best practices encourage us to avoid doing anything to raw form response data but instead use other sheets to manipulate ...
- 7,080
0
votes
Looking for help on formula while pulling data from a different GSheet using Index & Match?
IMPORTRANGE is a pretty expensive, so you should probably just make this query in the source spreadsheet. But if that is not possible, then here is my proposed solution:
=LET(salesman, "AAA",...
- 1,070
0
votes
How to extract multiple email addresses in a Google doc?
REGEXREPLACE & REGEXEXTRACT
=INDEX(TOCOL(LET(
str,REGEXREPLACE(TOCOL(A:A,1), "[{\[\]}""]", "¥"),
REGEXEXTRACT(str, REGEXREPLACE(
str, "mail¥:¥(.*?)¥&...
- 7,080
2
votes
Accepted
Insert sequence number and embed it to my data
I would use the MAP function instead of QUERY.
=MAP(TOCOL(UNIQUE(A:A),1), LAMBDA(r, {r,
JOIN(" ",
LET(v, SORT(FILTER(B:B, A:A=r)),
INDEX(SEQUENCE(ROWS(v)) & "-&...
- 7,080
3
votes
How to combine formulatext() and arrayformula()?
Try this:
=MAP(A1:A10,LAMBDA(r,FORMULATEXT(r)))
See map() and How to convert any Formula to ArrayFormula in Google Sheets.
- 31
0
votes
How can I return a list of IP addresses that doesn't include any I've already used?
You can use COUNTIF with FILTER to return your list without any IPs that have been used:
=FILTER(list, LEN(list), NOT(COUNTIF(used, list)))
# Using your sample data
=FILTER(F4:F, LEN(F4:F), NOT(...
- 7,080
2
votes
Accepted
Generate a customized ID showing 10 numbers
Use text(), like this:
=sort(text(sequence(42; 1; 0); "00000000A1"))
For this to work, the formula needs an array enabling wrapper such as arrayformula(), index() or sort().
See text().
- 15.3k
1
vote
Accepted
Calculating total score of a column
If your weighting is in column A;
Your values are in B2:G100
Your formula could be
=BYCOL(B2:G100, LAMBDA(col,
SUMPRODUCT(A2:A100, col)))
If your weighting is relative, your formula could be:
=...
- 7,080
1
vote
Select subrange of an array-returning formula like GOOGLEFINANCE()
Rather than invoking the sledgehammer that is the QUERY function, you can slice an array using this named function:
SLICE(array, offset_rows, offset_cols, num_rows, num_cols)
Returns a specified ...
- 111
0
votes
Script that reads rows and columns in a sheet and returns strings with frequency of occurrence
A Script is Unnecessary
You are seeking a script but you can use Sheets native functions to achieve your goal with much less effort and complexity.
Formula
=QUERY(
TOCOL(
BYROW(TOCOL(A1:A,1), ...
- 7,080
0
votes
How to find a string in a range and sum all the values inside multiple brackets for that string within a single cell in Google Sheets?
=QUERY(
BYROW(
INDEX(
SPLIT(
TOCOL(
REGEXREPLACE(
SPLIT(C3:C33,")"),
"^[+-]",),
1),
"(")),...
- 7,080
0
votes
Accepted
Data from second importrange function is in the wrong column
You are using a comma between the IMPORTRANGE functions, but should be using a semicolon.
-----------------
{{A,B}, {C,D}} = | A | B | C | D |
-----------------
...
- 7,080
1
vote
Keyboard shortcut for jumping to Google Sheets formula bar
reviewing the above answers i discovered the best way to access the formula bar in Google Sheets. type " Control-j " ( which takes you to the " name " bar ) and than hit " ...
0
votes
Cannot create pie chart in which all values are identical
Is there a workaround for this?
Absolutely!
Don't create your pie chart using a list of identical text strings
Workaround.
1. Create your pie chart from a list containing at least 2 unique ...
- 7,080
1
vote
Accepted
Formula for calculating cumulative cost with a fixed rate
Formula with your data
REDUCE would work for your example. It uses an accumulator whose result is only returned once all the array values have been processed.
=REDUCE(,SEQUENCE(3), LAMBDA(a, v, a + v*...
- 7,080
0
votes
How can I automatically select and download rows as CSV based on checkbox states and then modify checkbox states after download?
This script saves a copy of the CSV in the drive folder of your choice and logs the download link and the parent folder link.
It uses Google's API to convert to CSV instead of doing it from the sheet ...
- 7,080
0
votes
Formula for calculating cumulative cost with a fixed rate
Put Month 1 and $100 in cells A2:B2.
Put Month 2 and =B2 + 100 in cells A3:B3.
Select cells A3:B3 and copy them down.
See Creating simple formulas.
- 15.3k
0
votes
In google sheets - how return *1.54M* as $1,540,000 with the same formula that I would return $1.35B* as $1,350,000,000?
Array Formula
IMPORTHTML(...) is a placeholder for your existing formula that returns the source data.
This aporoach multiplies numbers in the millions by 10^6, and billions by 10^9, and returns ...
- 7,080
0
votes
3D Formula Google Sheets - sum across sheets
function sum3D(ref, idx){
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
let sum = 0;
for (let i = 0; i < sheets.length; i++) {
if (i >= idx) {
let n = sheets[...
- 7,080
0
votes
How to aggragate data based on a keyword
Use split() and query(), like this:
=query(
filter(iferror(split(A2:A, " -"), 0), len(A2:A)),
"select Col1, sum(Col3) - sum(Col2)
where Col1 is not null
group by Col1
...
- 15.3k
1
vote
How to use Google Sheet as a time tracker
Use two instances of regexextract(), like this:
=regexextract(D3, "\d{2}:\d{2}$") - regexextract(D3, "\d{2}:\d{2}")
Format the formula cell as Format > Number > Duration.
- 15.3k
0
votes
Why does my script return a TypeError when comparing getA1Notation to a string?
There is a syntax error in activeSheet.getRange.getA1Notation(). When using getRange as a way to get a range from the sheet assigned to a variable like activeSheet, choose one of the forms of this ...
1
vote
Sharing a single tab in Google sheet as a viewr?
It's not possible to share a single tab. If you are fine with users getting view-only access, you may want to explore Looker Studio. See the Gallery for examples of what you can do with it.
In the ...
- 15.3k
0
votes
Google Sheets countif attendance sheet, quarterly report
=INDEX(LET(
rng,Sheet1!B2:AZ, qtr,3,
mRng,OFFSET(rng,-1,,1),
qArr,INT((MONTH(mRng)+2)/3),
SUM(BYROW(rng, LAMBDA(r,
SUMPRODUCT(r, qArr=qtr)>0))*1)))
rng stores the range without the ...
- 7,080
1
vote
Accepted
How to add a Google sheet time driven trigger that runs offline?
How to add a Google sheet time driven trigger that runs offline?
Apps Script runs on Google's servers, not in your computer. A time-driven trigger will fire at the time specified, regardless of ...
- 15.3k
0
votes
Match criteria THEN CountIFs
QUERY Function
=QUERY(Sheet1!A:G, "Select A, COUNT(A)
Where F != 'beta' AND G matches '.*apple.*'
Group by A Label COUNT(A) 'Count'",1)
- 7,080
0
votes
Dynamic data range column in formula based on header value in google sheets
This approach uses two IMPORTRANGE calls to each Sheet. The first is for the label row to identify the correct row numbers. The second is for smallest range possible that includes both those columns.
...
- 7,080
Top 50 recent answers are included