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))))


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 ...


1

=ARRAYFORMULA(IFERROR(A1:A&LOWER(SUBSTITUTE(ADDRESS(1, ROW(A2:A)-MATCH(A1:A, A1:A, 0), 4), 1, ))))


1

you can try SORTN with 2 as 3rd parameter: =SORTN(E1:I, ROWS(E:E), 2, 2, 1)


1

You just need to type =k$11 instead.


1

Your list is almost complete: .GDOC .GDOCX .GDRAW .GFORM .GLINK .GMAP .GSHEET .GSLIDES .GTABLE Other possible Google extensions include: .GG, .GS, .GPI, .GPV


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 ...


1

This can now be accomplished via the Show edit history feature. Right-click on a cell and select "Show edit history". It will show you who made the edit, when it was made, and what changed. You can then go back and forward through cell edits.


1

A generic and elegant solution that works for any given day of the week (N-day): Cell B2: date to change to the next N-day of the week; Cell B3: next N-day of the week: Mon=1,Tue=2,...; =B2+MOD(B3-WEEKDAY(B2,2),7) For a specific day of the week, in this case Monday (day=1), the formula can be 'hard-coded' to =B2+MOD(1-WEEKDAY(B2,2),7) Note that by ...


Only top voted, non community-wiki answers of a minimum length are eligible