New answers tagged

0

If I understand your question correctly (i.e. that you have a source Sheet in which the SUM lives in row X and if you add rows above it then your IMPORTRANGE call will not follow along (as it would if it was in the same document) and subsequently you lose the reference to that SUM cell) then one way around this is to give that SUM cell a name (right-click on ...


1

EDIT (based on your comment) No mixed characters, it will always be a single character or a repeat of a single character For a draggable formula use =SUM(INDEX(IFERROR(SEARCH(B10,$B$2:$E$7)*LEN($B$2:$E$7)))) Original answer For a single auto-expanding formula please use =INDEX(IFERROR(VLOOKUP(B10:B13, QUERY(FLATTEN(SPLIT(REGEXREPLACE(TEXTJOIN(&...


0

Given your additional information that there will be "[n]o mixed characters, it will always be a single character or a repeat of a single character," this formula should produce the result you want: =ArrayFormula(QUERY(FLATTEN(SPLIT(REGEXREPLACE(QUERY(FLATTEN(W2:Y),"SELECT * WHERE Col1 Is Not Null"),"(.)","$1~"),"~...


0

Try this formula out? =ArrayFormula(SUM(IF(ISNUMBER(FIND($W7, SPLIT(REGEXREPLACE(CONCATENATE($W$2:$Y$5),".{1}", "$0,"), ","))),1,0))) Sources: Finding occurrences of a String in a Cell REGEXREPLACE adding Delimiters My addition to the Source Material: "CONCATENATE($W$2:$Y$5)"


3

Please use the following (Adjust ranges to your needs) =SORTN(QUERY({INDEX(ROW(D2:D)),D2:E}, "select Col2, Col3 where Col2 is not null order by Col2, Col1 desc"), 9^9,2,1,1) How the formula works We add a virtual column based on each row to our query and order the query. We then -in ...


1

To avoid the issue with the FILTER function, use a query instead. =QUERY({January!A2:O; February!A2:O; March!A2:O}, "where Col1 is not null",0)


1

I added a new sheet ("Erik Help") with the following array formula in D2: =ArrayFormula(IF(B2:B="",,SUMIF(IF(ROW(B2:B),ROW(B2:B)),">="&ROW(B2:B),C2:C))) The initial IF(B2:B="",, just leaves cells blank if the corresponding cell from Column B of that row is also blank. The opening IF(ROW(B2:B),ROW(B2:B)) of the ...


Top 50 recent answers are included