Search code examples
dateif-statementgoogle-sheetslambdagoogle-sheets-formula

Sum in range until value change

I'am trying to use this formula to make it work

=ARRAYFORMULA(IF(ISDATE_STRICT(S2:S) ; (MATCH(MAX(AB2:AB),AB2:AB;0)-1) ; "" ))

If there is a date in Column "S" I want it to display the sum of the blanks that would appear if in Column "S" is text

=ARRAYFORMULA(IF(ISDATE_STRICT(S2:S) ; ArrayFormula(MATCH(FALSE ; ISBLANK(AB2:AB) ; 0)-1) ; "" ))

I've tried this one as well but I only get 0's as a result.

Any idea how I can make it work?

Here is the sample sheet.

https://docs.google.com/spreadsheets/d/19f5phXeAwXwrKbWz7njgbznmurOav72GUuo_5IGcbls/edit?usp=sharing

Solution

  • in Q2 use:

    =ARRAYFORMULA(IF(ISBLANK(
  I1:INDEX(I:I; ROWS(I:I)-1));
 {N2:INDEX(N:N; ROWS(N:N))\ 
  I1:INDEX(N:N; ROWS(N:N)-1)};
  I1:INDEX(O:O; ROWS(O:O)-1)))

    in X2 use:

    =INDEX(LAMBDA(x; IFNA(VLOOKUP(x; QUERY(VLOOKUP(ROW(x); 
 IF(ISDATE_STRICT(x); {ROW(x)\x}); 2; 1); 
 "select Col1,count(Col1) group by Col1"); 2; 0)-1))
 (Q2:INDEX(Q:Q; MAX((Q:Q<>"")*ROW(Q:Q)))))

    enter image description here

    UPDATE:

    we start with column Q. we can take a range Q2:Q but that range contains a lot of empty rows. the next best thing is to check the last non-empty row and set it as the end of the range resulting in Q2:Q73. but static 73 won't do in case the dataset would grow or shrink so to get 73 dynamically we take the MAX of multiplication of Q:Q not being empty and row number of that case eg. Q:Q<>"" will output only TRUE or FALSE so what we are getting is

    ...
TRUE * 72 = 1 * 72 = 72
TRUE * 73 = 1 * 73 = 73
FALSE * 74 = 0 * 74 = 0
...

    so the formula for getting Q2:Q73 is:

    =Q2:INDEX(Q:Q; MAX((Q:Q<>"")*ROW(Q:Q)))

    it could also be:

    =INDEX(INDIRECT("Q2:Q"&MAX((Q:Q<>"")*ROW(Q:Q))))

    but it's just long to type... next, we use the new LAMBDA function that allows us to reference cell/range/formula with a placeholder. simple LAMBDA syntax is:

    =LAMBDA(x; x)(A1)

    where x is A1 and we can do whatever we want with the 2nd (x) argument of LAMBDA like for example:

    =LAMBDA(a, a+a*120-a/a)(A1)

    you can think of it as:

    LAMBDA(A1, A1+A1*120-A1/A1)(A1)

    or as just:

    =A1+A1*120-A1/A1

    the issue here is that we repeat A1 4 times but with LAMBDA we do it only once. also, imagine if we would have 100 characters long formula instead of A1 so the final formula with lambda would be 300 characters shorter compared to "old way" formula.

    back to our formula... x is the representation of Q2:Q73. now let's focus on VLOOKUP. basically, the idea here is that IF Q column contains a date we return that date, otherwise we return the last date from above. simply put:

    =ARRAYFORMULA(VLOOKUP(ROW(Q2:Q73); 
 IF(ISDATE_STRICT(Q2:Q73); {ROW(Q2:Q73)\Q2:Q73}); 2; 1))

    enter image description here

    as you can see Y2, Y3 and Y4 are the same so all we need to do is to count them up and later take away one to exclude Q2 but include just Q3 and Q4 eg. 3-1=2. for that we use simple QUERY where the output is:

    date             count
30.06.2022       3

    so all we need to do is to pair up dates from Q column to QUERY output for that we use the outer VLOOKUP where the output is as follows:

    3
#N/A
#N/A
9
#N/A
#N/A
...

    now is the right time for that -1 correction while we have these errors coz ERROR-1=ERROR and 3-1=2 so after this -1 correction the output is:

    2
#N/A
#N/A
8
#N/A
#N/A
...

    and all we need to do now is to hide errors with IFERROR and the output is column X