1

I found a way to do it myself. Only took 4 hours :( =Index(Responsibilities!$B$2:$B$7, MATCH(DATEVALUE(CONCATENATE(MONTH(today()),"/1/",Year(today()))),Responsibilities!A2:A7,0),1)


1

EDIT As very correctly noticed in the comments, there is a hasty, common, still unforgivable mistake in my original formula. The correct formula is =QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)-1&"") Please do notice that in order to get the current month one needs to subtract 1 month. WHY? Because in a ...


1

This formula did the trick: =sort(A2:A,REGEXEXTRACT(A2:A,"^\w+"),true, VALUE(REGEXEXTRACT(A2:A, "\d+$")), true) My first attempt was missing the string to value conversion.


1

There are two criteria: Must have A in column A Column B must have a value. So, use SUMIFS Formula: =SUMIFS(B1:B5,A1:A5,"A",B1:B5,">0") Here is the screenshot of where I used the formula: Also, make sure format is set to duration where you are performing the sum.


1

You believe that the Total "P&L%" generated by your formula is incorrect. This is true. The formula uses sum totals of various fields, but the result is meaningless. It needs to calculate the aggregate result on a unit-by-unit basis. The correct P&L% is 16.69%. Try this formula in cell G10: =F10/ARRAYFORMULA(SUMPRODUCT(C3:C9 / B3:B9,D3:D9)) ...


1

Please, see Test file Formula in cell C2 (with results in columns C:D) aggregates your data by quarters returning last date of respective quarter and average amount for this quarter. =QUERY( {B2:B, ARRAYFORMULA(eomonth(date(year(EOMONTH(A2:A,0)),(quotient(MONTH(A2:A)-1,3)+1)*3,1),0))}, "Select Col2, AVG(Col1) Where Col1 > 0 Group by Col2 Order by ...


1

={ARRAYFORMULA(ifna(VLOOKUP(A2:A1000, SORT({Log!B2:B, Log!A2:A, Log!E2:E}, 2, false), 3, 0)))} Enter this formula is cell G2 of Sheet="Demo". The logic is: SORT: sorts the ranges of Logs by the Timestamp, descending. Note that the whole of the "Logs" data is NOT sorted; "Domain" (Column B) (moved to the first column so it can ...


1

No, you can only freeze the first n rows or columns. But you can create a macro that would freeze the initial columns and hide those that you don't need (and another to un-hide those columns back).


1

SPLIT will work: =SPLIT(A2,"?",0,1) Here, SPLIT views the split character (i.e, "?") as the same whether there is 1, or whether there are 500 of them in a row. Of course, this can be applied to an entire column as well: =ArrayFormula(IF(A2:A="","",SPLIT(A2:A,"?",0,1))) Your post shows "?" as the ...


1

Matthew, if your decimal number were in A1, try this: =INT(A1)&"d "&TEXT(A1,"h\h m\m")


1

You can use the following formula under conditional formatting: =AND(C5<$B$2,C5>$A$2) (Adjust ranges to your needs)


1

You would need to alter your "regular expression" in the sample regex from: ((?:[[:alpha:]]+\s?)+) to: (?:[[:alpha:]]+\s?)+ Following that, your formula will work just fine. =textjoin("",true,arrayformula(if(iserror(REGEXEXTRACT(B2,Criteria!$A$2:$A)),"",choose(1,REGEXEXTRACT(B2,Criteria!$A$2:$A))))) You could also use [A-Za-z] ...


1

Hunter, it's always tough to write formulas without access to an actual spreadsheet and data. Even with what you shared here, there are a lot of unknowns. So you'll always get the fastest and most accurate solutions if you share a link to a sample spreadsheet, being sure to set permission to "Anyone with the link can edit." That said, based on the ...


1

Here's the short version, Julio: 1.) First, you'll want to have "January 2020" instead of just "January," etc. If you only have the words, a pivot will try to order them alphabetically, since they are just words. This would leave "February" before "January," etc., which isn't what you want. In addition, once January ...


1

We can still sort of split a string on each position using MID. If we throw in an COUNTIF as per your attempt it could look like: =ARRAYFORMULA(COUNTIF(MID(A1,SEQUENCE(LEN(A1)),1),"C")) Though, a bit simpler would be: =SUMPRODUCT(MID(A1,SEQUENCE(LEN(A1)),1)="c") Or in case of a longer string (let's stick with the cat =)): =SUMPRODUCT(...


1

I just had a similar problem and solved it nicely with the MINIFS function: =MINIFS(SAC!E$2:E$23, SAC!A$2:A$23, ">=" & A2) In short: select the minimum value from the factor column, where the value in the depth column is equal to or greater than the given value.


1

You can do this using the search and replace feature in Google Sheets with regular expression and specified range. In the search and replace dialog use these values Find: ^/(.+) Replace: http://$1 Make sure you select the option "Search using regular expression" You can specify a cell range so you do not accidently replace values unintentionally. ...


1

It kept removing extra spaces, even when I placed a character in the front (e.g. =CHAR(160)&"text") and pasted those results over. Here's what worked for handling leading spaces (this solution won't accommodate text with trailing spaces): Determine the number of leading/training spaces, in B1 enter: =LEN(A1)-LEN(TRIM(A1)) Paste the original ...


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