New answers tagged google-sheets-arrayformula
1
=QUERY(A1:C, "select A,B,sum(C)
where C is not null
group by A,B
label sum(C)'Days'", 1)
=ARRAYFORMULA(IFERROR((VLOOKUP(UNIQUE(A1:A&B1:B), QUERY({A1:A&B1:B, A1:C},
"select Col1,Col2,Col3,sum(Col4)
where Col4 is not null
group by Col1,Col2,Col3
label sum(Col4)'Days'", 1), {2,3,4}, 0))))
0
=ARRAYFORMULA(IFERROR(
IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(
IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(
REGEXEXTRACT(A2:A&"", "00(\d{18})02"),
REGEXEXTRACT(B2:B&"", "00(\d{18})02")),
REGEXEXTRACT(C2:C&"", "00(\d{18})02")),
REGEXEXTRACT(A2:A&"", "00(\d{18})15")),
REGEXEXTRACT(B2:B&"", "00(\d{18})...
0
do it like this:
=ARRAYFORMULA(SUM(IF((K2:K22>=1) * (K23:K22<=5) * (U13="1-5"), 1, )))
1
=ARRAYFORMULA(
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "02(.*)37")))=14,
REGEXEXTRACT(A2:A&"", "02(.*)37"),
IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "02(.*)37")))=14,
REGEXEXTRACT(B2:B&"", "02(.*)37"),
IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "02(.*)37")))=14,
REGEXEXTRACT(C2:C&"", "02(.*)37"), ...
1
=ARRAYFORMULA(SUM(IFERROR(QUERY(IFERROR(IMPORTRANGE(
"1HrEGCH-MCfeGh952VkpONRfSoLbZVLir5BNgFWK0PH8", "inquiries.app date.bonus!A5:O")*1),
"select Col13
where Col13 is not null
and Col14 >="&DATEVALUE(TODAY()-30), 0))))
spreadsheet demo
1
The problem is that Column M and Column Nfrom the source spreadsheet include text and numbers / dates. Blank cells doesn't cause problems.
In both columns, you should remove the text values before passing the imported values to QUERY.
While it could apparently be "elegant" to have a single formula that does all the job, this makes the spreadsheet harder ...
0
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"))
0
It is doable but your data needs to be reorganized a bit:
cell B16:
={"Attention";
"Information";
"Lead";
"Contact";
"Sales";
"After-Sales"}
cell C16:
=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
VLOOKUP(B16:B21, FILTER({E2:E13, D2:D13}, B2:B13="Persona 1"), 2, 0),
"Website", 1), "Mailbox", 2), "Call", 3)*1)
cell D16:
=ARRAYFORMULA(SUBSTITUTE(...
0
I too thought this doesn't work, but it does.
First, set the locale for the sheet to your locale.
Select the cells you want to create the series in and set the format to date.
Enter the 2 dates that define the increment of the range.
Select those 2 cells, then hover over the bottom right corner, you'll get a +, then drag that to the end of the range.
...
1
=ARRAYFORMULA(IFERROR(A1:A&LOWER(SUBSTITUTE(ADDRESS(1,
ROW(A2:A)-MATCH(A1:A, A1:A, 0), 4), 1, ))))
1
that's a common issue which can be outsmarted like this:
=ARRAYFORMULA(QUERY(SUBSTITUTE(Regex!A:B, "'", "♦"),
"select Col2
where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1
limit 1"))
=ARRAYFORMULA(SUBSTITUTE(QUERY(SUBSTITUTE(Regex!A:B, "'", "♦"),
"select Col2
where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1
...
0
you can use ARRAYFORMULA instead of dragging it
=ARRAYFORMULA(TO_DATE(TEXT(ROW(INDIRECT("A"&DATEVALUE("2019-1-22")&
":A"&DATEVALUE("2019-1-29"))), "dd/mm/yyyy")))
0
=AVERAGE(IFERROR(QUERY(D7:D,
"where D is not null
offset "&COUNTA(QUERY(D7:D, "where D is not null", 0)) - 7, 0)))
0
delete everything in B column and paste this into B1 cell:
={"Price"; ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, H:J, 3, 0)))}
delete everything in E column and paste this into E1 cell:
={"Total"; ARRAYFORMULA(IF(C2:C<>"", C2:C*D2:D, ))}
0
try simple VLOOKUP with exact and partial matches and outputting 1st and 2nd adjacent cells
=ARRAYFORMULA(IFERROR(VLOOKUP(""&B2:B,
{REGEXEXTRACT(Maintenance!B8:B, TEXTJOIN("|", 1, B2:B)), Maintenance!C8:D},
{2, 3}, 0)))
0
=ARRAYFORMULA(REGEXMATCH(A1,TEXTJOIN("|",1,TRIM(SPLIT($H$3, ",")))))
0
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(PROPER(A1:A),
"Analyst|Assistant|Associate|Coordinator")))
or load them from a column:
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(PROPER(A1:A),
TEXTJOIN("|", 1, E2:E))))
0
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(C2:O<>"", "♦"&C2:O&"♠"&C1:O1, )),,999^99)),,999^99), "♦")), "♠")))
0
=ARRAYFORMULA(IF(LEN(A1:A), REGEXMATCH(B1:B&"", A1:A), ))
=ARRAYFORMULA(IF(LEN(A1:A), IF(REGEXMATCH(B1:B&"", A1:A), B1:B), ))
=ARRAYFORMULA(IF(LEN(A1:A),
IF(REGEXMATCH(B1:B&"", TEXTJOIN("|", 1, A1:A)), B1:B), ))
-1
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(C2:O<>"", "♦"&B2:B&"-"&C1:O1, )),,999^99)),,999^99), "♦"))))
0
The picture you inserted into Google Sheets' cell is hosted/located on Google's server (exact location/address is unknown). In other words - you "made a copy" of your picture from your device to Google Sheets.
Once the image is in the cell you are able to simply refer to that cell like:
And further, you are able to use this reference in formulas like:
...
0
cell D1 - (basic query): =QUERY(A1:A20, "select A")
cell G1 - (converted to numbers): =ARRAYFORMULA(QUERY(VALUE(A1:A20), "select Col1"))
cell I1: - (converted to numbers - shorter approach) =ARRAYFORMULA(QUERY(A1:A20*1, "select Col1"))
cell K1: - (as it should be) =ARRAYFORMULA(QUERY(IF((ISNUMBER(A1:A20*1))*(A1:A20<>""), A1:A20*1, A1:A20),
"select ...
0
=ARRAYFORMULA(IF(LEN(A3:A),
MMULT(IF(IFERROR(SPLIT(IF(LEN(A3:A), A3:A, ), " "))<>"", 1, 0),
ROW(INDIRECT("A1:A"&COUNTA(IFERROR(
QUERY(IF(IFERROR(SPLIT(IF(LEN(A3:A), A3:A, ), " "))<>"", 1, 0), "limit 1", 0)))))^0), ))
______________________________________________________________
=ARRAYFORMULA(IF(LEN(A3:A), LEN(REGEXREPLACE(A3:A, "[^\s]", ))+...
1
select Custom formula
type in =(A2<>2)*(A2<>4)
if you want to delete them completely, use this formula pasting it into C1 cell:
=FILTER(A:B, REGEXMATCH(TO_TEXT(A:A), "[^2|4]"))
and then copy range C:D and press CTRL + SHIFT + V
0
=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(REGEXREPLACE({
IF(REGEXMATCH(IFERROR(SPLIT(A1:A, " ")), ",$"), , IFERROR(SPLIT(A1:A, " "))),
IF(REGEXMATCH(IFERROR(SPLIT(A1:A, " ")), ",$"), IFERROR(SPLIT(A1:A, " ")), )},
",$", )), , 999^99))))
0
no need to have multiple ARRAYFORMULA functions in one formula
=ARRAYFORMULA({
QUERY(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!B2:B},
"select Col1,avg(Col2) where Col1>0 group by Col1"), "select Col1"),
ROUND(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!B2:B},
"select avg(Col2) where Col1>0 group ...
1
In the end this the solution in Google Sheets.
=ARRAYFORMULA(IF((E10="")*(F10="EXPENSE"), "ISSUE", ))
Thanks to @user0 for their suggestion.
0
paste in B2 cell:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, data!$A:$C, {2, 3}, 0)))
paste in E2 cell:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&B2:B&D2:D,
TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(LEN(data!A2:A), "♦"&data!A2:A&data!B2:B&
IF(LEN(data!D2:G), REGEXEXTRACT(data!D1:G1, "^..")&"♠"&data!D2:G, ), ))
,,...
1
This simple Query formula is your solution.
=query({resp3columns!A:D;resp3columns!A2:A,resp3columns!E2:G;resp3columns!A2:A,resp3columns!H2:J},"select * where NOT Col2='' order by Col1",1)
In the screenshot you can see variations with different numbers of data columns (3, 4, 6)
ADDED BONUS
You get your data ordered as well by Name or Model or any other ...
1
drag down the A9 cell with the blue square as much down as you need
retrieve your formula from C2 cell
formula generator spreadsheet demo
1
={Sheet1!A1:D1;
ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN({
IF(LEN(Sheet1!B2:B); "♣"&Sheet1!B2:B&"♣"&Sheet1!C2:C&"♣"&Sheet1!D2:D;)\
IF(LEN(Sheet1!E2:E); "♣"&Sheet1!E2:E&"♣"&Sheet1!F2:F&"♣"&Sheet1!G2:G;)\
IF(LEN(Sheet1!H2:H); "♣"&Sheet1!H2:H&"♣"&Sheet1!I2:I&"...
0
=ARRAYFORMULA(SUM(HLOOKUP(C1, 'LB dinamic'!A1:H,
{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}, 0)))
=SUM(INDIRECT("'LB dinamic'!"&
ADDRESS(2, MATCH(C1, 'LB dinamic'!A1:1, 0), 4)&":"&
ADDRESS(ROWS('LB dinamic'!A:A), MATCH(C1, 'LB dinamic'!A1:1, 0), 4)))
=SUMPRODUCT(QUERY(TRANSPOSE('LB dinamic'!A1:M), "where Col1='"&...
2
=ARRAYFORMULA(IF((A2:A="")*(B2:B="EXPENSE"), "ISSUE", ))
for one cell/row evaluation paste in C2 and drag down:
=IF((A2="")*(B2="EXPENSE"), "ISSUE", )
spreadsheet demo
0
to derive week number:
=ARRAYFORMULA(IF(LEN(B2:B), WEEKNUM(B2:B, 2), ))
to convert it into the 1st day of each week number:
=ARRAYFORMULA(IF(LEN(B2:B), VLOOKUP(WEEKNUM(B2:B, 2),
{WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
DATEVALUE(MAX(B2:B)))), 2),
TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&...
0
you can solve this with ARRAYFORMULA like for example:
=ARRAYFORMULA(Baking!A2:A)
if you want to skip empty cells use FILTER like:
=FILTER(Baking!A2:A, Baking!A2:A<>"")
if you want to continue with next sheet use {} brackets to construct range (paste in A2 cell and drag to the right with a blue square):
=FILTER({Frying!A2:A; Baking!A2:A; Stewing!A2:A}, ...
1
paste in AH27 cell:
=ARRAYFORMULA(QUERY(IF(O7:O21=22, VLOOKUP(22, O7:X21, {3,4,5,6,7,8,9,10}, 0), ),
"where Col8 is not null", 0))
paste in AT27 cell:
=ARRAYFORMULA(QUERY(IF(O7:O21=22,
Y7:Y21+AC7:AC21+AE7:AE21+AG7:AG21+AI7:AI21+AK7:AK21, ), "where Col8 is not null", 0))
1
COUNTIF with OR could be solved like this:
=ARRAYFORMULA(SUM(N(REGEXMATCH(A1:A, "new|active"))))
finding for one value:
=ARRAYFORMULA(COUNTA(IFERROR(FILTER(A1:A, A1:A="Zone A",
REGEXMATCH(B1:B, "new|active")))))
finding for all values:
=QUERY(A:B, "select A,count(B)
where B matches 'active|new'
group by A
label count(B)''"...
1
=ARRAYFORMULA(IF(LEN(C32),
IF(SUM(N(REGEXMATCH(C6:C28, "SA|PA|MA|PB|MB")))=C32, "0",
SUM(N(REGEXMATCH(C6:C28, "SA|PA|MA|PB|MB")))-C32), ))
alternatives:
=IF(C32<>"", IF(COUNTIF(C6:C28, "SA")+
COUNTIF(C6:C28, "PA")+
COUNTIF(C6:C28, "MA")+
COUNTIF(C6:C28, "PB")+
COUNTIF(C6:C28, "MB")=...
0
in one formula it would be:
=ARRAYFORMULA({IMPORTRANGE("ID", "NameOfTab!A2:A1100"),
IF(IMPORTRANGE("ID", "NameOfTab!A2:A1100")<>"", "Y", )})
0
the "" is part of IFERROR not IF therefore try:
=QUERY(ARRAYFORMULA(IF(COUNTIF(B2:B,
IFERROR(QUERY('Form Responses'!B2:G, "select G where B !=''", 0)))=0,
IFERROR(QUERY('Form Responses'!B2:G, "select B,G where B !=''", 0)), )),
"where Col1 is not null", 0)
1
...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 outputs #N/A - No matches are found in QUERY/FILTER evaluation. - the issue is that #N/A is only in the 1st cell:
but array expects that matrix on both sides ...
0
=ARRAYFORMULA(IF(A1:A<>""; IF(A1:A < TODAY(); "required"; ); ))
=IF(A1 < TODAY(); "required"; )
-1
you can use internal functions like:
=ARRAYFORMULA(SUBSTITUTE(AC7:AG24, ".", "/"))
=ARRAYFORMULA(REGEXREPLACE(AC7:AG24, ".", "/"))
or even internal formatting:
1
={""; ARRAYFORMULA(IF(B4:B<>"", IFERROR(VLOOKUP(A4:A,
QUERY(QUERY(QUERY(A4:C,
"select A,sum(B),sum(C)
where A is not null
group by A
label sum(B)'',sum(C)''", 0),
"select Col1,Col2-Col3
label Col2-Col3''", 0),
"where Col2 > 0", 0), 2, 0)), ))}
0
...understanding the ARRAY_LITERAL ERROR:
until both queries/filters output something then all is good:
however if one of those queries/filters doesn't have anything to output it outputs #N/A - No matches are found in QUERY/FILTER evaluation. - the issue is that #N/A is only in the 1st cell:
but array expects that matrix on both sides to be same (4 ...
0
Delete range A2:A and paste this into A2 cell and for next month just type August in A1 cell:
=ARRAYFORMULA(TEXT({ROW(INDIRECT("A"&
DATEVALUE(DATE(2019, MONTH(A1&1), 1))&":"&
DATEVALUE(DATE(2019, MONTH(A1&1), 7)))); "Week 1"; ROW(INDIRECT("A"&
DATEVALUE(DATE(2019, MONTH(A1&1), 8))&":"&
DATEVALUE(DATE(2019, MONTH(A1&...
1
={"Remaining Principal";
ARRAYFORMULA(QUERY(IF(LEN(E7:E), MMULT(TRANSPOSE((ROW(E8:E)<=
TRANSPOSE(ROW(E8:E)))*{B4; -1*INDIRECT("E"&ROW(E8)&":E"&ROWS(E:E)-1)}),
SIGN(E8:E)^0), ), "offset 1", -1))}
demo spreadsheet of running total
alternative: https://wamoyo.github.io/amortization/
0
If values in A1 & B1 cells output values as a result of formulae this won't be possible to "restrict" via Data Validation. In such cases, the common practice is to wrap your formulae in IF statement and direct it towards outputting nothing or error if the condition is not met. A1 then be like:
=IF(B1=0, your_A1_vlookup_formula_here,
IF(...
0
={""; ARRAYFORMULA(IF(K4:K>0,
IF(VLOOKUP(A4:A, QUERY({A4:A, K4:K},
"select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)-
VLOOKUP(A4:A, QUERY({A4:A, L4:L},
"select Col1,sum(Col2) where Col1 is not null group by Col1", 0), 2, 0)=0, 11,
IF(VLOOKUP(A4:A, QUERY({A4:A, M4:M},
"select Col1,sum(Col2) where Col1 is not null group by ...
1
=QUERY(UNIQUE(A1:B),
"select Col1,count(Col2)
where Col1 is not null
group by Col1
label count(Col2)''", 0)
=ARRAYFORMULA(IFERROR(VLOOKUP(C:C, QUERY(UNIQUE(A1:B),
"select Col1,count(Col2)
where Col1 is not null
group by Col1
label count(Col2)''", 0), 2, 0)))
Top 50 recent answers are included
