New answers tagged google-sheets-arrayformula
1
vote
generating URLs with VSTACK (or HSTACK)
I manage to circumvent the issue, by using Curly-brace Array Formulas. The following is working fine:
={""; ARRAYFORMULA(IF(ISBLANK(A1:A),,HYPERLINK("https://<snip>"&A1:A,...
- 141
0
votes
Need help nesting multiple =IF into one formula?
Use ifs() to test multiple conditions, like this:
=ifs(
isblank(F43), iferror(ø),
regexmatch(F43, "MILLING"), "RND / MILLING",
regexmatch(F43, "PROFILING"), &...
- 15.7k
2
votes
Need help nesting multiple =IF into one formula?
The syntax is
=if(condition, do when true, do when false)
You can chain several conditions
= if(condition1,
do when true,
if(condition2,
do when true,
do when ...
- 271
0
votes
Sum rows that match a criteria
To add the numbers in rows where column A1:A equals "A", use this pattern:
=sum(filter(B1:D, A1:A = "A"))
To add the numbers where column A1:A equals "A" or "B"...
- 15.7k
0
votes
Accepted
Sorting multiple columns using formula
Use BYCOL
=BYCOL(A1:D4, LAMBDA(col, SORT(col)))
BYCOL LAMBDA SORT
- 7,482
0
votes
Accepted
Sum rows that match a criteria
=SUM(ARRAYFORMULA(IF(A1:A5="A",SUM(B2:D4))))
gives wrong number, because you're summing all the data first inside the IF: SUM(B2:D4). This gives SUM of all the values.
You should only ...
- 3,365
0
votes
Sum rows that match a criteria
You were on the right track but should use SUMPRODUCT instead of SUM as it is already an array formula:
=SUMPRODUCT((A1:A4="A")*B1:D4)
SUMPRODUCT
This is equivalent but shorter than:
=...
- 7,482
0
votes
Sum rows that match a criteria
Assuming I understood correctly your question, you can simply write:
colA
colB
colC
(row 2)
A
1
1
(row 3)
B
3
3
(row 4)
B
3
3
(row 5)
A
1
1
(row 8)
B*
(row 9)
Result
12**
cellA8 has a variable ...
- 21
0
votes
How can I count annual days spent in different countries when stays overlap calendar years?
Array of Dates rather than Date Ranges
One approach is to convert your data into a list of all dates (days) you spent in each location. Then, it is a simple matter to aggregate the data by Year, ...
- 7,482
Top 50 recent answers are included