4
votes
Accepted
Why does my conditional formatting not work with this formula?
Use indirect() when referring to another sheet in a conditional formatting custom formula rule, like this:
=not($B$15) * ifna(match(left(C19), indirect("Code Sheet!X4:AD4"), 0)) * ifna(match(...
- 13.2k
3
votes
Accepted
How could one use filter in multiple columns to use the maximum value and return the row name that it belongs to?
When you join ranges with an { array expression }, the ranges must be of the same height (when joining horizontally) or the same width (when joining vertically).
You apparently want to join ranges ...
- 13.2k
3
votes
Accepted
Google Sheets lookup with contains condition
Use filter() and search(), like this:
=single( iferror( filter(Table2!B$2:B, len(Table2!B$2:B), search(Table2!A$2:A, D2)) ) )
See filter() and iferror().
The single() function is one of the many ...
- 13.2k
3
votes
Accepted
How do I check a set of numbers in different columns against numbers in different columns
Use this conditional formatting custom formula rule for the range A2:F2:
=countif($H2:$M2, A2)
- 13.2k
3
votes
Accepted
Formula to generate unique sequential IDs for items in a column that don't already have IDs
You can create the sequential IDs with this formula:
=ArrayFormula("AB"&TEXT(SEQUENCE(ROWS(A2:A)),"000000"))
Then, you can set a filtered range, excluding those values already ...
- 1,233
3
votes
Insert space after every character of a text string
I solved my problem with the following formula:
=TRIM(
REGEXREPLACE(
A1, "(.)", "$1 "))
Credit to @Erik Tyler for suggesting the TRIM function which provided a much ...
2
votes
Google sheets range of sheets?
Another way to do this is to use the INDIRECT statement. You will still need to list the sheet names, but if they are sequential or if you have a list with the names, you can copy and paste them into ...
- 21
2
votes
Accepted
How to make a self-adjusting two-way concatenation table?
Please, see the file
A4 contains following formula:
=ARRAYFORMULA(
TRANSPOSE(
FILTER(
TRANSPOSE(
FILTER($A$1:$F$1&TRANSPOSE($A$1:$F$1),$A$2:$F$2)
),$A$2:$F$2)
)
)
The idea ...
- 791
2
votes
Convolve two arrays on Google Sheets?
This may be done as follows by using lambdas or Named Functions.
It is as simple as doing something like this:
=CONV(dice1, dice2)
This works as follows. The below code may be copy-pasted into a ...
- 403
2
votes
Accepted
How would you successfully return the row name of the maximum value in a column based on conditions in the rest of the row?
Use filter(), like this:
=filter(
G27:H60,
M27:M60
=
max(
filter(
M27:M60,
regexmatch(I27:I60, "(?i)^(CADET|C/Amn|C/A1C|C/SrA)$")
)
)
)
- 13.2k
2
votes
Cumulative Sum without Script
(2022 update: With respect to other solutions and their authors, the other solutions are now outdated, and should no longer be used due to simplicity or performance reasons, unless backwards-...
- 403
2
votes
Accepted
Extracting hashtags from a cell - fixing a 99% working regex formula
Try this one:
=trim(regexreplace(A2, "(\r+)|(\n+)|((^|\s)[^#]\S*)|([^#\w\s]\S*)", " "))
- 2,338
2
votes
Accepted
Google Sheets MATCH/VLOOKUP formula searching Date with AM/PM "cannot find value"
The error message tells that the value in cell A6 is not the exact date 2023-02-15 but the datetime 2023-02-15 18:00:00, and that none of the values in the column 'On-site Totes Pilot'!O1:O match that ...
- 13.2k
2
votes
Accepted
LOG function in Google Sheets
While it is the case that there are some locale-based differences between how logarithmic functions are named in Excel, a review of your Google sheet exposed the problem unrelated to the function ...
- 3,381
2
votes
Return last value that isn't an error in each row
Assuming your explanation of the logic involved is complete, and the examples pictured are truly representative of the real spreadsheet, all of the rules can be replaced with the rule:
In column F, ...
- 690
2
votes
Accepted
SUMIF column by comparing two date columns
For this task, either the QUERY or FILTER functions together with SUM will work best.
In both cases, the MAP function enables passing each pair of lookup values, "Ticker Symbol" and "...
- 3,381
2
votes
Accepted
Is it possible to do a more concise IF x = 1 OR x = 2 OR x = 3 in Google Sheets?
Since the result of MOD(ROW(),7) can have 2 inappropriate values (0 and 1) and 5 suitable values (2..6), it makes sense to check the condition for a shorter sequence:
MOD(ROW(),7)>1
In this case, ...
- 819
2
votes
Accepted
Custom formula is not working in google sheet
To apply the rule to all cells in the row, use column absolute addressing in the custom formula rule, like this:
=$D12
See absolute and relative references.
- 13.2k
2
votes
Can I sort cells in Google Sheets with a key function?
You can achieve the desired result using built-in functions like this:
=sort(A1:D4,byrow(C1:D4,lambda(row,max(row))),0)
2
votes
Accepted
How can I use COUNTIFS to compare different date formats?
I'm looking for the date matches (don't care about the timestamp itself).
If you want to use COUNTIFS to count all the rows where both conditions are matched and you want to ignore times, then ...
- 3,381
2
votes
Accepted
QUERY function to return records with multiple criteria
Use the matches keyword together with a regular expression, like this:
=query(
Sheet1!A1:D,
"select A, C, D
where B matches '" & join("|", split(A1, ", ", ...
- 13.2k
2
votes
Accepted
I have multiple unequally sized rows that I want to combine into a single matrix. How can this be done?
You could try something as simple as =tocol(code_f3!A1:ZZZ,1) to put all the results (minus blanks) into a single column. ZZZ is the maximum number of columns Google Sheets allows (18278).
2
votes
Accepted
Formula that looks for any matching substrings between two cells
One way to do that is to create a regular expression, like this:
=regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|"))
When B1 is foo bar, the ...
- 13.2k
2
votes
Accepted
Selecting only rows corresponding to Thursday in a column of dates
As a formula: =filter(A2:A,weekday(A2:A)=5))
Using the filter option in the taskbar: Use 'filter by condition'/'custom formula is' and set the formula to =weekday(A2:A)=5
1
vote
How to find value below a specific cell in another sheet
=WRAPROWS(TOCOL(A:D,1,1),2)
TOCOL stacks the range into a single column:
ignore is set to 1 to skip blanks
scan_by_column is set to TRUE or 1
WRAPROWS converts the column into 2 column rows by ...
- 27
1
vote
Accepted
Displaying Indian Number system (Lakhs and Crores) in Google Sheets
Conditional number formats are limited to two conditions plus a default when neither condition is satisfied. The conditions are limited to a comparison operator and a value.
To use an Indian number ...
- 13.2k
1
vote
Accepted
Are named functions able to be shared?
Named functions are automatically shared, because they are stored per spreadsheet, not per user. You can also import named functions from one spreadsheet file to another.
See the named functions help ...
- 13.2k
1
vote
Accepted
Formula to increase and decrease amount by a percentage found in other cells
You can try this formula for the 5 weeks in just the first cell:
=MAP(B7:B11,BYROW(E7:H11,LAMBDA(e,SUMPRODUCT(e,TRANSPOSE(G31:G34)))),LAMBDA(bonus,perc,bonus*(IF(perc>0,1,SI(perc<-1,0,1+perc)))))...
- 1,233
1
vote
Google sheets - what function can I use to expand MAXIFS function to an entire column?
Thanks Martin.
Thanks as well to anonymous who went ahead and edited the sample sheet for me, using XLOOKUP. I'll post it here for those who need: =ArrayFormula(XLOOKUP(A2:A,ContactLog!A4:A,ContactLog!...
- 23
1
vote
Accepted
How to add a missing : to an input
It can be surprisingly complex to check that a free-form text string is a valid time value. Use regular expressions and ifs(), like this:
=lambda(
digits, error,
lambda(
result,
ifs(
...
- 13.2k
Only top scored, non community-wiki answers of a minimum length are eligible